This tutorial will discuss context transition and nested Iterators. You’ll learn the importance of these entities and how they can be a good source for optimization.
If you recall, context transition happens when a row context changes into an equivalent filter context. You need to be aware of what’s happening when a context transition occurs in your iterators because it can often generate slow results.
Nested Iterators Versus CALCULATE
If a context transition is invoked during an iteration, a materialization table is created. This means that context transition is invoked via the formulas in your DAX. The table is an uncompressed data that’s sent to the formula engine.
To demonstrate, the first measure is for Big Sales that gets the Current Price if it is greater than or equal to 500.
The next measure is for the average of the Big Sales with nested iterators.
The last measure gets the average of Big Sales without a nested iterator and a CallBackDataID.
Run the measure with the nested iterator. Make sure to clear the cache before running, and then turn on the Server Timings and Query Plan.
You can see that it generated 3 scans, 2 CallBackDataIDs, and 15,003 rows. This is a cause for concern because there are only 101 rows for the output. This happened because the measure has two iterators or X functions.
This is the fully expanded form of the measure that was ran:
It’s iterating the Fact Sales table with AVERAGEX. It then reiterates again with the CallBackDataID using SUMX. The shortened format of this measure has an implied CALCULATE function that invokes context transition and materializes the table. That’s why it pulled 15,003 rows, even if there are only 101 rows for output.
Run the other measure that doesn’t have nested iterators. It’s still iterating the Fact Sales table using AVERAGEX but the Current Price is outside the function. It filters out the products where the current price is greater than or equal to 500 and then computes for the average.
You can see that it only has 2 scans, 104 rows, and doesn’t have a CallBackDataID. So if you use CALCULATE, you can get rid of the nested iterators and CallBackDataIDs.
***** Related Links *****
DAX Functions In Power BI: Using Iterators
How To Use An Iterating Function In Power BI
DAX Calculation – Filter Context Transitions
Conclusion
There are many causes for a slow performing DAX. One of them is due to nested iterators which cause unnecessary context transitions. These iterators materialize more rows than needed, which causes your queries to slow down.
The best solution for this is to place it inside CALCULATE. This creates less work for the formula engine and maximizes the capabilities of the storage engine.
Nick
