Slow Switch Statement

Question

Hey,&nbsp;I'm trying to create one measure, that will return other measures based on another unrelated 'Measure' table, that I will use as a column in a Matrix. The below DAX expression does the trick, but its SO slow. I think its calculating every measure, rather than just the selected few. Anyone got any ideas around this at all? I've tried using IF(), and i've tried using SWITCH(TRUE(),"Actual",[Actual]........). I can't find a way around it, but it's a real necessity for the users to be able to filter between the different measures as they need&nbsp;MasterP&amp;LMeasure = VAR SelectedMeasure = SELECTEDVALUE('Measure Select'[Measure Select])RETURNSWITCH(SelectedMeasure,"Actual",[Actual],"Forecast",[Forecast],"Budget",[Budget],"Client Budget",[Client Budget],"Prior Year",[Prior Year],"Actual vs Forecast",[Actual vs Forecast],"Actual vs Budget",[Actual vs Budget],"Actual vs Client Budget",[Actual vs Client Budget],"Actual vs Prior Year",[Actual vs Prior Year],"Forecast vs Budget",[Forecast vs Budget],"Forecast vs Client",[Forecast vs Client Budget],"Forecast vs Prior Year",[Forecast vs Prior Year],"Actual All Years",[Actual All Years],"Forecast All Years",[Forecast All Years],"Budget All Years",[Budget All Years],"Client Budget All Years",[Client Budget All Years],"Actual vs Forecast All Years",[Actual vs Forecast All Years],"Actual vs Budget All Years",[Actual vs Budget All Years],"Actual vs Client Budget All Years",[Actual vs Client Budget All Years],"Forecast vs Budget All Years",[Forecast vs Budget All Years],"Forecast vs Client Budget All Years",[Forecast vs Client Budget All Years])

az38 · Answer

Hi&nbsp;Anonymous&nbsp;
try step 2 and step 3 in this&nbsp;https://community.powerbi.com/t5/Community-Blog/Performance-Tuning-DAX-Part-1/ba-p/976275 workaround by&nbsp;Greg_Deckler&nbsp;

parry2k · Answer

Anonymous&nbsp;do you know if this is the switch is slow or the measures used in switch are slow, what happens if you use these measures directly, and see if these measures are not performing as expected.
&nbsp;
&nbsp;

anonymous · Answer

The measures are taking just short of 5 seconds each to load per performance analyser. If I have 4 measures filtered in the measure table, i was expecting about 20 seconds max for the table. Instead its taking 80/90 seconds to load this single switch measure, with no other visuals or measures on the page. Googling it, it seems that switch does perform this way, and calculates every measure regardless of its use - just wondering if anyone had any ideas at all on alternative solutions

v-xicai · Answer

Hi Anonymous&nbsp;,
&nbsp;
Az38' suggestion is great.&nbsp; In addition, you may improve the performance of measures used in your SWITCH formula, such as [Actual], [Forecast], [Budget],,,etc.&nbsp;
&nbsp;
For example:
&nbsp;
Use SELECTEDVALUE() instead of VALUES()
&nbsp;
Use ISBLANK() instead of =Blank() check
&nbsp;
Do not use scalar variables in SUMMARIZE()
&nbsp;
Use variables instead of repeating measures inside the IF branch
&nbsp;
You may refer to the tips:
&nbsp;
Improve Power BI Performance by Optimizing DAX 
&nbsp;
A comprehensive guide to Power BI performance tuning
&nbsp;
Best Regards,
Amy&nbsp;
&nbsp;
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

anonymous · Answer

Thanks for your reply! I've spent a good while trying to apply this to my Dax and can't figure it out. Step 2 relies on the number of entries in a table but mine is an unlinked table with one instance of each in the table. Step 3 I don't really see how I can apply it either. If you can give any guidance on how I could specifically apply them to my situation id really appreciate any help

Forum Discussion

Slow Switch Statement

8 Replies

Related content

Switch statement in power query

Slow Switch Performance

Calculation groups to replace slow IF/SWITCH?

Nested switch statements?

IF Statement or Switch