Force DAX SWITCH function to use strict (lazy) short-circuit evaluation

Question

Set up: Similar to this question on a MSDN forum, I have a measure that switches between various other measures (some of them much more complex than others). The measure looks like this (my actual measure has more cases):

VariableMeasure =
VAR ReturnType =
    SELECTEDVALUE ( ParamReturnType[ReturnType] )
SWITCH (
    ReturnType,
    "NAV", [Nav],
    "Income", [Income],
    "ROI", [Roi],
    "BM", [Benchmark],
    BLANK ()
)

---

Context: The reason for the switching measure is to have the ability to choose which measures to display on a report by choosing the ReturnType with a slicer and then the selected measures show up as column headers in a matrix visual. For example, my matrix may look like this:

(As you can see, it's not showing BM since it's not selected.)

---

Problem: The problem is that when I just have NAV selected the measure is still nearly as slow as with everything selected despite it being a cheap measure to compute. When I profile the query with DAX Studio, it takes about 2.7 seconds for what should be a simple query. Indeed, if I comment out the lines with measures other than [Nav] in the switching measure the performance improves drastically, running in less than 100 milliseconds (30x faster). DAX Studio shows that the formula engine (FE) is responsible for about 99.5% of the 2.7 seconds whereas the storage engine (SE) uses less than 20 milliseconds.

---

Research: I've read the following SQL BI articles, which mention lazy/strict evaluation and short-circuiting.:

Understanding eager vs. strict evaluation in DAX

Optimizing IF and SWITCH expressions using variables

My measure is analogous to the last example in the second link but does not benefit from "short-circuit evaluation" as it does in their case.

This Power BI Community question is similar but provides no additional insight.

This Power BI Usergroup Community post references a couple more interesting articles but those haven't ultimately led me to a resolution, unfortunately.

---

Question: How can I get each measure in the SWITCH to evaluate independently of the other cases since building a query plan to accommodate all of the cases simultaneously results in poor performance everywhere?

I'm open to suggestions or workarounds that resolve my issue even if it doesn't narrowly answer this question.

---

Additional information: I have a hunch that the query engine may indeed be strictly evaluating the switch function but only after building a generalized query that can support all of the cases and since the different measures are built with dissimilar logic, this is highly inefficient (and also where my situation is not analogous to the SQL BI example I referred to).

---

Update:

Microsoft has addressed this problem at least to some extent in a May 2021 update of Power BI:

https://powerbi.microsoft.com/en-us/blog/power-bi-report-server-may-2021-feature-summary/#perf

Skirl

It's a known problem. It's even worse - the performance you get often depends on the order of your measures in the Switch statement. Calculation groups are a solution as long as they are an option. There is a (rather strange) work-around (see comments under the article): joyfulcraftsmen.com/blog/… – Pietra

Thanks, @RADO. That really helped point me in the right direction. I do work with Power BI Premium so calculation groups might be an option and a separate workaround is useful too. – Skirl

Alas, the suggested workaround doesn't improve anything in my case. – Skirl

Sorry to hear... I am suffering from a similar issue. I ended up writing calculations back into the data warehouse and adding them as a new fact table. Lost drill down but reports are now fast – Pietra

I just now finished reading a new SQLBI article that might apply to your use case: it's on HASONEVALUE() and ISINSCOPE(), and implications for optimizing filter and grouping conditions: sqlbi.com/articles/distinguishing-hasonevalue-from-isinscope I haven't actually tested it so I'm not posting it as an answer, but let us know if it helped. – Zebulon

@AlexisOlson Have you tried considering adding measures to VAR and then using them inside SWITCH? Not sure but this is what I tried. ```` Measure = var selected = SELECTEDVALUE(main[Group]) var msr1 = [Measure1] var msr2 = [Measure2] return SWITCH(selected,"A",msr1,"B",msr2) ```` I read that VAR makes the LAZY execution. – Coverture

Looks like there is another SWITCH improvement in Nov 2021. powerbi.microsoft.com/en-us/blog/… There is no documentation however how to design the pattern of the code inside the function. Should we use the most popular SWITCH result as the first tested condition or the last? – Transistorize

As of late 2022 there is now new functionality named Field Parameters that addresses this type of use case: learn.microsoft.com/en-us/power-bi/create-reports/… – Dessalines

Answer 1

In the bottom of that article Marco answers a similar thread as yours. Here's a couple of ideas to try:

1] Convert switch to switch( true, ... )

from Dax.Guide/Switch You can pass true as the first argument with conditions in the body, like:

VariableMeasure =
    VAR ReturnType =
        SELECTEDVALUE ( ParamReturnType[ReturnType] )

    var FinalValue =
        SWITCH (
            true,
            ReturnType = "NAV", [Nav],
            ReturnType = "Income", [Income],
            ReturnType = "ROI", [Roi],
            ReturnType = "BM", [Benchmark],
            BLANK ()
        )
return FinalValue

2] Skip [Nav] if not used

Or branch your code skipping [Nav] references:

VariableMeasure =
    VAR ReturnType =
        SELECTEDVALUE ( ParamReturnType[ReturnType] ) 
        // tip: SELECTEDVALUE lets you set a default to return instead of blanks

    var UsingNav = 
        ReturnType = "NAV"

    var UsingNavFastValue =
        SWITCH (
            true,
            "NAV", [Nav],
            // ...
            "BM", [Benchmark],
            BLANK ()
        )

    var WithoutNav =
        SWITCH (
            ReturnType,
            "Income", [Income],
            // ... 
            "BM", [Benchmark],
            BLANK ()
        )

return if(
    UsingNav, UsingNavFastValue, WithoutNav )

References:

• Dax.guide:Using Switch

Josiejosler