Visual Calculations — DAX Just Got Easy: Part 1
If You Are A Beginner To Power BI And Want To Learn DAX, Then This Blog Is For You
Welcome back, dear readers, to another exploration into the world of DAX. Those who are new here, can access all my previous articles on DAX through my curated list — Elevate DAX.
Now, with the unveiling of Microsoft’s latest preview feature, Visual Calculations, in the February 2024 update, the landscape of DAX writing and comprehension is going to change.
Let’s face it: DAX hasn’t always been the friendliest language to tackle. But with Visual Calculations now in the picture, the barriers to entry are notably lower. This not only simplifies DAX syntax but also offers a familiar ground for those who come from Excel or with limited coding experience.
Introduction
Simply put, Visual calculations are calculations confined to the scope of a visual. When I say scope here, it means it is defined and executed directly on a visual.
Unlike DAX measures, it is not stored in the model but in the visual itself. Thus one need not to worry about complexities around the model. You also need not to be aware about the evaluation contexts as Visual calculations often computes results row-by-row within the filter context of the visual.
Furthermore, leveraging aggregated data within the visual typically results in improved performance compared to traditional measures.
Enough theory now, lets see how it works on the next section.
How To Add
Since this feature is still in preview, it has to be enabled through :
Options and Settings ➡️ Options ➡️ Preview features ➡️ Visual Calculations.
Once this is done you can follow below steps:
- Adding Visual Calculations
To add a visual calculation on a visual, First Select that visual and then from the Calculations section in Home tab, select New Calculation - Utilizing the Interface
Below the selected visual, you’ll now find a Formula bar and Visual matrix.
Formula Bar ➡To write Visual calculation
Visual Matrix ➡Data of the visual shown in matrix form. Any new visual calculation that you add, will show up here.
How to Write
Lets understand this with an example where we aim to display the profit value from the previous row.
- Using DAX measures
When employing DAX measures, there are a few methods to obtain the previous row value:
In the first method, we acquire the previous date in the current filter context and store it in the ‘PreviousRow’ variable. This variable is then utilized within a Calculate filter expression to retrieve the profit from the preceding row.
Similarly, in the second method, we utilize the OFFSET window function to fetch the profit value from the -1 or previous row relative to the current row. The Calculate function is then employed to compute the profit for the retrieved -1 row.
Although these DAX expressions yield the correct results, they might be challenging to grasp for newcomers to DAX due to their complexity.
This is where Visual Calculation comes to our aid. Let’s explore how.
2. Using Visual calculations
With Visual calculations, all we need to do is write a simple expression in the formula bar of the Visual calculation. The ‘PREVIOUS’ function, specific to Visual calculations, serves this purpose.
See how straightforward it is to obtain the profit from the previous row using Visual calculations. No need to delve into the intricacies of DAX expressions;
Now, we can write DAX in simple English words through Visual calculations.
Lets see another example, this time we will do a Running Total. As you may recall, I’ve previously shared various methods to achieve this using DAX in my blog:
While these methods were undoubtedly easy to understand, I must emphasize that nothing quite compares to the simplicity of Visual calculations.
With Visual calculations, we can effortlessly utilize the ‘RUNNINGSUM’ function to achieve the desired result. Take a look at the expression and its output below:
Just like the ‘RUNNINGSUM’ function mentioned above, there are several other Visual-specific functions available, including MOVINGAVERAGE, EXPAND, NEXT, FIRST, LAST, COLLAPSE and more. Microsoft Power Bi team has provided a full list of such functions, which you can access here.
Important points to be kept in mind while using Visual Calculations:
- These functions are exclusively meant for Visual calculations and cannot be used in regular DAX expressions for measures and calculated columns.
- Only values/fields that are already part of the Visual can be referenced in Visual calculations.
Conclusion
As I write my final thoughts here, you all must now be agreeing with me Visual calculations are super easy. Even those with no prior knowledge of DAX can now dive into it without being daunted by its complexities.
This is just Part 1 which merely scratches the surface of Visual calculations. There is a lot more to it. I’ve just covered it briefly, stay tuned for more detailed blogs about how Visual calculations work and what more can be done with it.
Update: You can read Part 2 👇
If you have any questions about Visual calculations or would like to share your feedback, please don’t hesitate to leave a comment. Your input keeps me motivated.
Connect with me through my social media handles; I look forward to hearing from you!
Want to know DAX better, you can read blogs from my Elevate DAX series.
Don’t forget to subscribe to
and join our Power BI community
