In this blog, we are going to dive into row context in DAX Power BI. Another way to call it is the iterating calculation engine, but it is called row context from a more technical perspective. You may watch the full video of this tutorial at the bottom of this blog.
DAX Power BI: Row Context & SUMX
The way that you get into row context is by using iterating functions in Power BI such as SUMX. SUMX returns the sum of an expression evaluated for each row in a table.
The second parameter of SUMX is the expression which stands for any logic that works with the table that we specify.
Following this example, remember how we generated this measure?
What happened first is that the evaluation context is applied. Afterwards, we jumped into the Sales table behind the scenes after every individual result.
After that, we ran some logic in every single row of that table and saved them in memory. Then, from those results in the memory, we finally summarized everything to get the results in the Total Sales column.
In summary, the evaluation context is done first, and then every single row is evaluated through row context because SUMX is an iterating function.
With this in mind, you can actually get really advanced with your expressions.
You can write IF statements or SWITCH statements, or any type of logic that can be done in a single row within an iterating function like SUMX.
SUMX is not the only iterating function we can use. There are also functions like AVERAGEX and MINX where calculations are also done in row context.
The iterating engine is more comprehensive than the filter context because it looks at everything left over in a column after all the filters are in place.
Row Context On Total Quantity
You can see here that we have our Total Quantity measure which takes the sum of the Quantity column.
We can actually calculate the same result using row context instead of filter context.
For this, we can create a new measure called Total Quantity (Iteration), use SUMX, and then put in the Sales table while referencing the Quantity column.
After that, we can now bring in our new measure for comparison. We are simply calculating exactly the same result for the two measures.
However, the results were calculated by using two different calculation engines in Power BI – the one on the left uses filter context while the other uses row context.
Although there is not much of a difference when working with simpler models, more computations are happening inside iterating functions.
If you will be working with much larger data sets and models, it is better to use the SUM function instead of SUMX. The earlier example was only for the sake of comparison, so it is unnecessary to overload the calculation more than needed.
However, for calculating something like the Total Sales, it is necessary to use an iterating function since there was no way we could calculate it using SUM based on the data that we had.
These are things that you should think about. Hopefully, it is more clear to you what is happening behind the scenes on these calculations.
DAX Power BI: Iterating Functions Vs Aggregating Functions
Based on personal experience, when I used Microsoft Excel, I never really had to think about what is going on with the calculation engines when running calculations.
But in Power BI, you need to think about when it is best to use an iterating function or an aggregating function. This gets more complex as you use more advanced formulas and combine them together.
With this in mind, it is important to get a good grasp of these concepts before you move on to more complex things like cumulative totals or moving averages.
***** Related Links *****
Optimizing Your Power BI Formula Using Advanced DAX Functions
The Difference Between SUM vs SUMX In Power BI
Using Iterating Functions SUMX And AVERAGEX In Power BI
Conclusion
Power BI is amazing because of all the high-quality analytics it is capable of, and it all starts with a good understanding of the row context.
Even with the more advanced stuff, you should think about the context of a particular result to understand what is being calculated.
That is all I wanted to cover with regard to iterating functions in Power BI. Next, we’ll go over additional things to consider about context.
All the best,
Sam
