Diagnosing Switch-Related Performance Problems In Power BI DAX Using EvaluateAndLog

The Switch function is often a cause of performance problems in DAX. Last year a number of optimisations were made that fixed the most common issues (and as a result you should ignore older blog posts that you may find on this subject) but some scenarios still remain where performance can be bad; Marco and Alberto wrote a great post recently outlining some of them that I encourage you to read. How do you even know whether your measure is slow because of your use of the Switch function though? Trying to read query plans, as Marco and Alberto do in their post, isn’t usually practical and commenting out branches in a Switch to see if there is a performance change can be very time-consuming. Luckily, the new EvaluateAndLog DAX function can help you diagnose Switch-related performance issues.

To illustrate this I built a very simple Power BI dataset with a fact table called Sales and a table called DisconnectedTable used for choosing the measure to display in a report (I know you can use field parameters for this but this is just a demo).

The Sales table looks like this:

…and the DisconnectedTable table looks like this:

There are two basic measures that sum up the values in the Sales and Tax columns:

Sales Amount = SUM('Sales'[Sales])

Tax Amount = SUM('Sales'[Tax])

There are also two measures that display either Sales Amount or Tax Amount based on the selection made on DisconnectedTable. One is called Dynamic Measure Optimised:

Dynamic Measure Optimised =
SWITCH (
    SELECTEDVALUE ( DisconnectedTable[Measure] ),
    "Sales Amount", [Sales Amount],
    "Tax Amount", [Tax Amount],
    BLANK ()
)

…and one is called Dynamic Measure Unoptimised:

Dynamic Measure Unoptimised =
SWITCH (
    SELECTEDVALUE ( DisconnectedTable[Key] ),
    1, [Sales Amount],
    2, [Tax Amount],
    BLANK ()
)

Now let’s assume you have a report with a slicer linked to the Measure column on DisconnectedTable and a table showing the Product column and either the Dynamic Measure Optimised or Dynamic Measure Unoptimised measure:

Both measures return the same value, but as Marco and Alberto show in their post since Dynamic Measure Optimised looks for the selected value on the column that is used in the slicer – the column called Measure on DisconnectedTable – it will perform better than Dynamic Measure Unoptimised, which looks for the selected value on the Key column of DisconnectedTable, which is not used in the slicer.

How can we prove this though? If you wrap the expressions used in each branch of Switch with EvaluateAndLog, like so:

Dynamic Measure Optimised =
SWITCH (
    SELECTEDVALUE ( DisconnectedTable[Measure] ),
    "Sales Amount", EVALUATEANDLOG ( [Sales Amount] ),
    "Tax Amount", EVALUATEANDLOG ( [Tax Amount] ),
    BLANK ()
)

Dynamic Measure Unoptimised =
SWITCH (
    SELECTEDVALUE ( DisconnectedTable[Key] ),
    1, EVALUATEANDLOG ( [Sales Amount] ),
    2, EVALUATEANDLOG ( [Tax Amount] ),
    BLANK ()
)

…and then run Jeffrey Wang’s DAXDebugOutput tool and connect it up to your Power BI Desktop file, you can see the difference between the two measures for the DAX query that populates the table visual in the report.

When using Dynamic Measure Optimised and with Sales Amount selected in the slicer, the DAX query for the table visual generates two DAXEvaluationLog events, one for the body of the table, one for the grand total row:

This is what you would expect and this is the optimal pattern: both DAXEvaluationLog events only mention the Sales Amount measure, the measure whose value is displayed in the slicer. However, if you use Dynamic Measure Unoptimised with Sales Amount selected in the slicer, you’ll see two extra DAXEvaluationLog events being generated where Power BI is paying the cost for analysing and executing the unselected branch for Tax Amount just to result an empty, unused result in the end:

It’s these evaluations of unused Switch branches that can cause query performance problems. So my advice is if you’ve got a lot of complex measures that use Switch you should study Jeffrey’s blog carefully use EvaluateAndLog to see if any optimisation is necessary.

[Thanks to Jeffrey Wang for his help writing this post]