SWITCH performance problem

[Anonymous]

Hi recently i noticed serious performance issues on my production datasets and i created small dataset which is showing what the problem is.
I have create 4 type of measures 

1. Original :  just doing some random stuff
2. Slow nested with 3 levels of nesting. Here i do swtich with selectedavlue from other table, and based of selection i do always same calcualtion. Idea is just to have 10 possibilities to see that measures is getting slower. I have 3 levels in depth. It looks like this
   
   Slow Nested L1 =

   SWITCH(SELECTEDVALUE('Table 2'[Value])

   ,1,[Fast Original]

   .....

   ,10,[Fast Original]

   ,[Fast Original])
3. Fast nested calcualtion is just direct refecencing of same measures used in Slow Nested. Here we dont have switch
4. VAR Bad Practice is similar like Slow nested but instead of evaluating only one measure based on selected values, here i declare 10 variable first, and then use variable values in switch. IT looks like this:
   
   VAR Bad Practice L1 =

   VAR v1 = [Fast Original]

   .....

   VAR v10 = [Fast Original]

   
   

   RETURN

   SWITCH(SELECTEDVALUE('Table 2'[Value])

   ,1,v1

   ....

   ,10,v10

   ,[Fast Original])

Now i do testing by changin value on switch on the right.

As you can see Slow nested is by far worst option and from my understanding it looks like all expreisons are being evaluated for no reason (only one with selectedvalue should be evaluated).
Another thing that came as suprasing is that "VAR bad practice" is performing good.

Was something changed in calculation engine?

PBIX File

[v-juanli-msft]

Hi @Anonymous 

I downlaod your pbix, i see the "duration" of "Slow nested l3" measure may increase when many selections have been selected.

It is hard to say why it is so strange.

Here is an article which explains the performance using variables in a "IF" or "Switch" measure.

In a summary, the consequence of this behavior can severely affect the performance of a well-structured set of DAX measures. Therefore, it is better to follow these guidelines when using variables:

• When the same DAX expression is evaluated multiple times within the same filter context, assign it to a variable and reference the variable instead of the DAX expression.
• When a DAX expression is evaluated within the branches of IF or SWITCH, whenever necessary assign the expression to a variable within the conditional branch – this will maintain the short-circuit evaluation optimization.
• Do not assign a variable outside an IF or SWITCH statement if the variable is used only within the conditional branch.
• The first argument of IF and SWITCH can consume variables defined before IF and SWITCH without it affecting performance.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-juanli-msft 

Thanks for getting back on this.

I know about this blog and actually latest reply from few days ago is from me.

I was even more confused because  "VAR bad practice" (this is bad practice described in blog post) is performing better that Slow Nested L3 which is writen following best practice described in this post.

After all i noticed that having measures calls in conditional branch will get engine calculate measures even if conditional condition is not met. Which is really strange since official documentation is saying that
"Any scalar expression to be evaluated if the results of expression match the corresponding value."