Optimising DAX: Practical Examples
Hello again, and welcome to the final post in the Optimising DAX series! We've covered VertiPaq storage, encoding, cardinality, relationships, model design, the two engines, data materialisation, and callbacks. Now it's time to put it all into practice with some concrete examples.
The Classic CALCULATE Trap
This was one of the most impactful examples from the workshop. Compare these two expressions:
-- Slow: forces table materialisation
CALCULATE([A Measure], FILTER(Table1, Table1[ColumnA] = "value"))
-- Fast: uses a bitmap filter
CALCULATE([A Measure], Table1[ColumnA] = "value")
The first version uses FILTER with an explicit table reference. The storage engine doesn't know which columns the FILTER might need, so it materialises the entire table and hands it to the formula engine.
The second version uses a direct column filter, which the storage engine handles with an efficient bitmap. It supports <, >, =, !=, and IN operators.
The DAX looks almost identical. The performance difference can be enormous. This is exactly the kind of thing that's very easy to miss if you don't understand the engine architecture - and exactly the kind of thing that suddenly seems obvious once you do.
Variables and IF.EAGER
Watch out for patterns where you end up scanning large tables multiple times. A common case:
IF([Total Sales] > 1000, [Total Sales], BLANK())
Here, [Total Sales] might be evaluated twice - once for the condition and once for the result. Two scans of the same data. You can fix this with a variable:
VAR _sales = [Total Sales]
RETURN IF(_sales > 1000, _sales, BLANK())
Or by using IF.EAGER, which evaluates both branches upfront:
IF.EAGER([Total Sales] > 1000, [Total Sales], BLANK())
Both approaches prevent the duplicate scan.
The Hidden Cost of Slicers
Here's a fun one (or not, depending on your perspective): every time Power BI renders a slicer, it runs a DISTINCT() scan on the underlying column to populate the list of values.
If you're using a flat table model, that scan has to traverse the entire (potentially enormous) table. With a star schema, it only scans the much smaller dimension table.
This is yet another point in favour of star schemas, and it's a cost that's easy to overlook because it happens automatically - for every slicer, on every page load.
Isolating Slow Queries
When you need to actually track down a performance problem, here's the approach that was recommended:
Start in Power BI using the Performance Analyzer to identify slow visuals. The timings show three sections:
DAX Query - the time spent executing the query. This is the bit you can optimise. Visual Display - the time spent actually creating the visual (unavoidable). Other - this is usually by far the longest and is almost entirely time spent waiting in a queue to execute the query. The only real way to reduce this is to reduce the number of visuals on the page.
Move to DAX Studio once you've found a slow visual. Copy the query, enable Server Timings and Query Plan, and start deleting bits until it's fast. This isolates exactly which part is causing the bottleneck. It's a bit tedious, but it works.
Wrapping Up
And that's the series! If you've made it all the way through - well done, and thank you for bearing with me. I really enjoyed this workshop and getting my head around what's going on under the hood. Even in scenarios where I might not need to actively optimise, I think it changes how you think about writing DAX, and that's definitely a good thing!
