There is a particular kind of slow query that makes no sense the first time you meet it. You open Server Timings and run it. The storage engine events are all there, and none of them look unreasonable. What is odd is when the first one starts.
Normally the first scan fires almost as soon as the query does. Here there is a noticeable gap between the query beginning and the first round trip to fetch data, and once that finally happens the rest moves along quite happily.
So the scans are not what is slow. During the part that is slow, nothing has gone near the data yet.
That gap is usually called formula engine wait time, and until recently a specific pattern involving calculation groups could make it enormous. Cory Laban, an engineer on the Analysis Services team, shipped a fix for it. I sat down with him to get the explanation first hand.
This is not a product announcement. It is an attempt to explain what the engine was doing in that gap, why it was written that way, and how to work out whether the fix does anything for you.
Two phases
Cory’s framing is the clearest I have heard, so I will use his.
“If you think of DAX like it’s a programming language, the C compiler will compile C into assembly code and then the CPU will execute the instructions that the compiler generated. That’s exactly what a database engine is as well. We take some human readable language, in our case DAX, and we do some stuff to it until we get our machine code, which is storage engine queries, and then we execute those storage engine queries.”
So there are two phases, and they are the same two phases any compiled language has.
DAX query | v+------------------------------------------------+| PHASE 1 compile || elaboration + static analysis + optimisation | <- the gap you see| no data is read in this phase |+------------------------------------------------+ | v+------------------------------------------------+| PHASE 2 execute || storage engine queries, against real data | <- the SE events+------------------------------------------------+
Almost always phase one is so fast you never think about it. This post is about the case where it is not.
What happens in phase one
Cory was careful to say these are not neat sequential stages. Elaboration and static analysis run at the same time, then there is a second round of elaboration, and optimisations happen throughout. But there are three kinds of thing going on.
Elaboration is inlining. Taking references to things and substituting them where they are referred to from.
Static analysis is checking the query is safe to run. The clearest example is circular dependency detection. If you write VAR X = Y and VAR Y = X and then evaluate X, something has to notice before the engine spins forever. So the engine walks the named dependencies from the root of each evaluation, and if it finds a cycle it raises an error instead of executing.
Optimisation is making the eventual plan cheaper. Spotting duplicate measure references that can be fused together, and generally arranging for the storage engine to scan as few rows as possible.
All of it happens before a single row is read.
What elaboration does with a calculation group
This is the part worth slowing down on, because the thing that went wrong is a distortion of the feature working exactly as designed.
Say you have a calculation group with a Month to Date item, and you write a query that selects Month to Date and evaluates Sales. What the engine actually receives is a filter on a calculation group column plus a measure reference. What it needs is Sales evaluated inside the Month to Date calculation item.
Elaboration is what turns the first thing into the second. It inlines the calculation item around the measure, and the result is the same as if you had hand written a Sales MTD measure yourself.
That is the entire value proposition of calculation groups. You define Month to Date once instead of writing it into forty separate measures. The engine assembles the combinations at query time so you do not have to maintain them.
So for a query with three measures filtered to one calculation item, the arithmetic should be simple:
Query: Sales, Profit, Revenue filtered to [MTD] Sales -> MTD Profit -> MTD 3 expansions Revenue -> MTD
Three measures, one item, three expansions. That is what you would expect and it is what the engine ought to produce.
What was actually happening
It was not producing three.
Suppose the calculation group also contains Year to Date and Quarter to Date, which you did not ask for. The engine expanded those too.
Same query. Calculation group contains MTD, YTD, QTD. Sales -> MTD, YTD, QTD Profit -> MTD, YTD, QTD 9 expansions Revenue -> MTD, YTD, QTD
Nine instead of three. Annoying, but at this scale nobody notices.
Now add a second calculation group, and have the items in the first one reference the second. This pattern already has a name, sideways recursion. It is a normal thing to build. One group does time intelligence, another does currency or scenario or comparison, and the items in one lean on the other.
The expansions multiply.
Two calculation groups, three items each, cross referencing. Sales -> MTD -> {MTD, YTD, QTD} YTD -> {MTD, YTD, QTD} 27 expansions QTD -> {MTD, YTD, QTD} ... and the same again for Profit, and again for Revenue
Twenty seven. And the general form is a product, not a sum:
expansions = measure references x items in calc group 1 x items in calc group 2 x ...
Which means it grows quadratically as your model grows, and real models are not built from three item calculation groups. Cory’s example:
10 measures x 10 items x 10 items = 1,000 expansions
What the query actually needed, filtered to one item in each:
10 measures x 1 item x 1 item = 10 expansionsA thousand expansions, of which nine hundred and ninety would be thrown away. All of it before the first storage engine query is issued, and none of it dependent on how much data you have. This is why the problem reproduced on a model with almost no rows in it. The cost is entirely in compilation.
It is worth being clear that the complexity of the DAX inside each item was not the driver. As Cory put it, forget about the complexity of the DAX itself and just pay attention to how the count scales. Complicated items make each expansion more expensive, but the explosion is in the number of expansions.
Why was it written that way
This is the part I always want in these posts, because a fix is much easier to trust when you understand why the slow thing existed.
The answer is not that anyone made a mistake. It is a seam between two features.
“The reason for not doing it was basically fear of missing static analysis. We had engineers working separately on implementing static analysis and implementing calc group expansion, way back when these were first implemented.”
Two capabilities, built independently, each correct on its own terms. The static analysis code needed to be certain it had seen every path that might contain a circular dependency. It could not be sure which calculation items would end up mattering, so it took the conservative route and insisted on all of them. Correctness first, which is the right instinct. The cost sat quietly in the join between the two, and only became visible when models got big enough to expose it.
The fix
Once the cause is stated, the fix almost states itself. The filter that tells you which calculation item the user actually wants is right there in the query. So use it.
“During the actual static analysis, why are we not using this filter to prune down the relevant calc items that we want to expand for each of these measure references?”
That is what shipped. The static analysis phase now expands the measure references and looks only at the relevant calculation items when checking whether a circular dependency exists between a measure and a calculation group. In Cory’s written words, it “bootstrapped calculation item branch pruning during the static analysis phase of DAX elaboration, by ensuring variable references to filter expressions were fully expanded”.
A thousand expansions becomes ten. The static analysis still happens and still catches what it was there to catch. It just no longer insists on inspecting every combination in the model to do it.
The catch, and it is a real one
The optimisation only kicks in when your calculation group is filtered with TREATAS.
If you filter it with FILTER, or if you have a relationship from another table to the calculation group name column and you filter that other table instead, there is a chance you still experience redundant formula expansions.
Power BI visuals generally use TREATAS for simple filters on calculation groups, so most report-generated queries can benefit from the optimisation.
So if your calculation group filtering comes from a report, you are almost certainly covered. If you hand write DAX with FILTER over a calculation group, you may not be.
How do you tell if it helped you
I pushed on this, because a fix you cannot observe is a fix you have to take on faith.
There is no flag. Nothing in Server Timings, nothing in a profiler trace, nothing in the query plan tells you whether the pruning kicked in. Cory confirmed it plainly.
But the TREATAS limitation turns out to have a useful side effect. Because the optimisation only applies to TREATAS, you can turn it off yourself and measure the difference:
- Capture the DAX your visual generates.
- Copy it.
- In the copy, swap the
TREATASfilters on the calculation group forFILTER. - Run both and compare the gap before the first storage engine event.
The FILTER version is, near enough, your query without the optimisation. The difference between them is what the fix is worth on your model. That is a genuinely nice property to fall out of a scoping decision.
Will it help me
There is a precondition before any of the shapes below matter, and it is easy to miss.
The static analysis step this fix optimises does not run for every query. Certain model and query artifacts require it. One example is a query scope calculated column, the kind you get when you write something like this:
DEFINE COLUMN Sales[Margin] = Sales[Amount] - Sales[Cost]EVALUATE ...
If nothing in your model or your query calls for that analysis, the engine was not doing this work in the first place and there is nothing here to speed up.
Assuming the analysis is happening, the honest answer is that it depends on how much your query throws away.
No calculation groups. No change. This lives entirely in calculation group expansion.
A calculation group you do not filter. Say you drop a four item calculation group onto a matrix and you want all four items as columns. No meaningful gain. You start with four and you finish with four. There is nothing to prune.
One calculation group, filtered down. Ten items filtered to one. Real, but modest. You avoid nine expansions you were going to discard.
Several calculation groups, cross referencing, filtered down. This is where it becomes dramatic, because you are cutting a product rather than a sum. Fifty items filtered to four, in a model where the groups reference each other, is the difference between compiling something huge and compiling something small.
Cory described the ideal case as a lean model with a few calculation groups where users filter most of the items away.
The framing I settled on during the call, and Cory agreed with it, is this. The problem was never the fifty calculation items you built. It was that a query wanting four of them still paid to compile the other forty six.
The measured improvement is around 40 percent, and the useful part is that it holds across the distribution. Roughly the same at the median as out at the extreme tail. What that is worth to you in wall clock terms depends entirely on where your model sits in the shapes above.
What it does not fix
Worth stating plainly so nobody arrives hoping.
- Storage engine duration. This is compile time. If your scans are slow, they are still slow.
- Direct Lake cold performance. Different problem entirely.
- Anything without calculation groups.
SWITCHstatements and the like are unaffected.
There is no trade off to weigh up. For the model and query shapes it targets it should simply be faster, and it is upstream of whether you are Import, DirectQuery or Direct Lake.
What a modeller can still do
The fix does not remove the value of building calculation groups thoughtfully. Cory’s advice:
- Keep calculation group usage simple.
- Group by the calculation group name column.
- Avoid referring to other calculation groups from inside a calculation item.
That last one is the sideways recursion that drives the multiplication. It is legitimate and sometimes it is the right design, but it is worth knowing what it costs.
Build your own repro
If you want to see it, Cory’s suggestion is to let the Power BI modelling MCP server do the tedious part. Ask it for a model with two calculation groups of fifteen items each, where the items in the first group refer to the second group, and the second group selects every item. Then query it and watch the gap before the first storage engine event.
Once you know what the shape is, it is easy to reproduce. It was working out what the shape was that took the time.
Availability
The change is live in the Power BI service and Fabric. It is not supported in Power BI Desktop, Azure Analysis Services or SQL Server Analysis Services as things stand. There is nothing to switch on. If you are on a capacity that has it, your queries are already being compiled this way.
What is next
Separately from this, there is now fusion across calculation items. Where a query would previously have issued several storage engine queries across calculation items, fusion can combine them into fewer. How many you started with depends entirely on how the items were authored, so there is no tidy formula for it.
That is a different piece of work with a different set of trade offs, and it deserves its own post rather than a paragraph here. More on that shortly.