Visual Calculations — DAX Just Got Easy: Part 2
Understand In Deep About How Visual Calculation Works And Limitations Associated With It
Hello readers. Welcome to the part 2 of visual calculation blog series and I assure you, its loaded with insightful details.
For those new to the concept, let me define what Visual Calculations are. They actually empower users to create new calculations directly on the visual itself. The best part? You don’t need to be proficient in DAX to write these calculations, as they utilize a mostly different set of functions that are incredibly user-friendly.
If you’re a Power BI beginner looking to learn DAX, them my Part 1 on Visual Calculation is tailor-made for you. I strongly suggest going through that before continuing here, as it lays the foundation for the more detailed discussion in this blog.
Coming back to this blog, I’ll be focusing on two primary aspects:
- How Visual Calculation Works
- Limitations
Without further ado, let’s dive right in.
How Visual Calculation Works
To explain the workings of visual calculations, I’ll be referencing the Previ Row calculation that was discussed in Part 1 of this series. For a detailed understanding of how to display it visually, I recommend revisiting the first part (link provided above).
See the screenshot below, it showcases both the calculation and its corresponding result in the matrix visual.
Upon examining the visual results, you’ll notice that the [Prv Row Profit’] is blank for the September 2013 row, as well as in the Total row for the year 2013 and the Grand Total. This raises the question: How does the DAX engine determine that there is no preceding month before September 2013 or a preceding year before 2013?
Curios to know? To shed light on this calculation, I’ve taken the DAX Query generated from this visual using the Performance Analyzer. The DAX expression is provided below, followed by a detailed explanation of each section.
// DAX Query
DEFINE
COLUMN '__DS0VisualCalcs'[Prv Row Profit] =
(/* USER DAX BEGIN */
PREVIOUS([Total Profit])
/* USER DAX END */)
VAR __DS0Core =
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL(
'financials'[Year], "IsGrandTotalRowTotal",
ROLLUPGROUP('financials'[Month Name], 'financials'[Month Number]), "IsDM1Total"
),
"SumProfit", CALCULATE(SUM('financials'[Profit]))
)
VAR __DS0VisualCalcsInput =
SELECTCOLUMNS(
KEEPFILTERS(
SELECTCOLUMNS(
__DS0Core,
"Year", 'financials'[Year],
"IsGrandTotalRowTotal", [IsGrandTotalRowTotal],
"Month_Name", 'financials'[Month Name],
"Month_Number", 'financials'[Month Number],
"IsDM1Total", [IsDM1Total],
"SumProfit", [SumProfit]
)
),
"Year", [Year],
"Month Name", [Month_Name],
"Month_Number", [Month_Number],
"IsGrandTotalRowTotal", [IsGrandTotalRowTotal],
"IsDM1Total", [IsDM1Total],
"Total Profit", [SumProfit]
)
TABLE '__DS0VisualCalcs' =
__DS0VisualCalcsInput
WITH VISUAL SHAPE
AXIS rows
GROUP [Year] TOTAL [IsGrandTotalRowTotal]
GROUP
[Month Name],
[Month_Number]
TOTAL [IsDM1Total]
ORDER BY
[Year] ASC,
[Month_Number] ASC,
[Month Name] ASC
DENSIFY "IsDensifiedRow"
VAR __DS0RemoveEmptyDensified =
FILTER(
KEEPFILTERS('__DS0VisualCalcs'),
OR(
NOT('__DS0VisualCalcs'[IsDensifiedRow]),
NOT(ISBLANK('__DS0VisualCalcs'[Prv Row Profit]))
)
)
VAR __DS0CoreWithInstanceFilters =
FILTER(
KEEPFILTERS(__DS0RemoveEmptyDensified),
OR(
OR(
'__DS0VisualCalcs'[IsGrandTotalRowTotal],
AND(NOT('__DS0VisualCalcs'[IsGrandTotalRowTotal]), '__DS0VisualCalcs'[IsDM1Total])
),
AND(NOT('__DS0VisualCalcs'[IsDM1Total]), '__DS0VisualCalcs'[Year] IN {2013,
2014})
)
)COLUMN Definition
The COLUMN function is employed to define the [Prv Row Profit] calculation. In essence, this results in the creation of a new column within the Visual level evaluation context. This new column is stored in a table variable named __DS0VisualCalcs .
__DS0Core Variable
This variable is a core calculation variable created in Power BI when a DAX measure is utilized in a visual. Essentially, it aggregates the fields present in the visual and defines the total and grand total rows. In our case, the [Total Profit] DAX measure is used in the visual, summarized over both Year and Month.
__DS0VisualCalcsInput Variable
This variable is used to set the input table for Visual Calculation. It contains all the fields present in the matrix visual, including the total rows. These fields are summarized in conjunction with the __DS0Core variable. Additionally, KEEPFILTERS function is used to consider the filter context as well.
Visual Shape
Visual Shape is a crucial concept from the perspective of visual calculations. The result of the __DS0VisualCalcsInput variable is stored in the __DS0VisualCalcs variable, with the Visual Shape defined.
Any DAX measure when created is stored in the model and the calculation inherently understands the model’s granularity and hierarchy. This is also what the __DS0Core Variable does for the [Total Profit] measure.
However, for Visual calculations, which are not stored in the model and operate only at the visual level, Visual Shape works to define the hierarchy at the row and column levels.
In our calculation, Visual Shape first defines the hierarchy of fields in the matrix rows using the AXIS Function. This can also be done for columns, but our matrix does not have any column fields.
Then, using the GROUP function, it first groups at the Year level and then at the Month level (Month Number is also considered as Month Name is sorted using that).
The ORDER BY function is used to set the sorting order of the result. Here, the result is sorted at the Year and then Month level. The DENSIFY function is used to define the densification process, which specifically means for any data if there was grouping involved.
__DS0CoreWithInstanceFilters & __DS0RemoveEmptyDensified Variable
These two variables serve as supplements to the __DS0VisualCalcs variable. Once the calculation comprehends the hierarchy, these variables ensure that filters are propagated to each level. Additionally, they remove any BLANK densified rows from the visual calculation, thereby providing accuracy to the data presented.
With this explanation, I hope you’ve gained some understanding of how visual calculations operate. There are many more functions associated with visual calculations that need to be delved into, which I plan to cover in future blogs.
Limitations
Currently, there are some limitations in using Visual Calculations compared to DAX measures. These need to be considered before deciding which one to use:
- Not A DAX Replacement
In its current state, Visual Calculations cannot replace DAX. Not all calculations can be performed using it, and for many complex calculations, one has to rely on DAX. - Limited Visuals Support
Visual Calculations cannot be created on all visuals. Some visuals, like Clustered Column Chart, Map, Tree Map, etc., are not yet supported. - No Reusability
Since Visual Calculations are not stored in the model, they cannot be reused or copied onto another visual. A fresh calculation has to be created each time. - No Export of Underlying Data
We cannot export underlying data from the visuals that have visual calculations. - Formatting Limitations
Conditional formatting, format strings etc. cannot be set on these calculations. One has to encapsulate the calculation inside the FORMAT DAX function to define a custom format. - Field Parameters are also not supported yet.
- Performance Consideration
Since a lot happens under the hood for Visual calculations, It may result in a performance that is slightly lower compared to DAX measures.
There are some other limitations, which you can read from here.
Conclusion
We have come to an end in this part. In conclusion, while Visual Calculations offer a fresh, user-friendly approach to performing calculations directly on visuals, they are not a replacement for DAX.
Nevertheless, Visual Calculations is definitely a revolutionary way to write calculations in Power BI. Going forward, I expect many of the limitations to be addressed by Microsoft, making Visual Calculations an even more integral part of Power BI.
Stay tuned for my upcoming blogs to dive deeper into Visual Calculations. Until then, keep learning Power BI and DAX!
Got questions or need more clarity? Please drop a comment. Your feedback is also invaluable, so don’t hold back on your thoughts.
Connect with me through my social media handles; I look forward to hearing from you!
Access previous stories in the Elevate DAX series:
Don’t forget to subscribe to
and join our Power BI community
