Diagnosing Calculation Group-Related Performance Problems In Power BI Using EvaluateAndLog

A few weeks ago I wrote a post showing how you can use the new EvaluateAndLog DAX function to diagnose performance problems relating to the use of the Switch function. Did you know that calculation groups can experience similar performance problems though? In some scenarios limited evaluation takes place for all calculation items, not just the one you expect; luckily you can use EvaluateAndLog to diagnose this too. In this post I’ll show you a simple example.

Let’s say you have a dataset containing the following table, called Sales:

There’s also a calculation group with three calculation items:

The Measure Value calculation item returns the value of SelectedMeasure but also uses EvaluateAndLog:

EVALUATEANDLOG( SELECTEDMEASURE() )

The Measure Value * 2 and Measure Value * 3 calculation items do the same thing but multiply the value returned by SelectedMeasure by two and three respectively, for example:

EVALUATEANDLOG( SELECTEDMEASURE() * 2 )

There’s another table in the dataset, with no relationship to the Sales table, called Calc Item Names containing a single column listing the three calculation items’ names:

Last of all, there is a measure called Sales Amount:

Sales Amount = SUM(Sales[Sales])

…and a measure called Dynamic Sales Amount Unoptimised that takes the selection made on the Calc Item Names column of the Calc Item Names table and uses it to dynamically select the calculation item with that name and apply it to the Sales Amount measure:

Dynamic Sales Amount Not Optimised =
VAR SelectedCalcItem =
    SELECTEDVALUE ( 'Calc Item Names'[Calc Item Names] )
RETURN
    CALCULATE ( [Sales Amount], 'My Calc Group'[Name] = SelectedCalcItem )

All this allows you to build the following report where a slicer controls the calculation item applied in the Dynamic Sales Amount Not Optimised measure:

As the name suggests, though, there’s a problem with the Dynamic Sales Amount Not Optimised measure that EvaluateAndLog can help uncover. Using Jeffrey Wang’s DAXDebugOutput tool to capture what happens when Measure Value is selected in the slicer, you can see three DAXEvaluationLog events. The first is the one you’d expect: since Measure Value is selected in the slicer, the Measure Value calculation item is evaluated:

The other two events are for the other two, unselected calculation items though – and similar to the Switch problem, some evaluation taking place for all calculation items can cause performance problems:

How do you avoid this problem? It turns out that if you use TreatAs to apply the calculation item inside Calculate, like so:

Dynamic Sales Amount Optimised =
CALCULATE (
    [Sales Amount],
    TREATAS ( VALUES ( 'Calc Item Names'[Calc Item Names] ), 'My Calc Group'[Name] )
)

You only get one DaxEvaluationLog event when the query runs and only the calculation item you selected is evaluated:

[Thanks to Krystian Sakowski for originally diagnosing the problem in this post and coming up with the fix]