Slow Switch Statement

[Anonymous]

Hey,

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

MasterP&LMeasure =

VAR SelectedMeasure = SELECTEDVALUE('Measure Select'[Measure Select])

RETURN

SWITCH(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]

)

[jordancole0517]

[jordancole0517]

I had an issue with something similiar and was able to get away with this via structure formatting of the visuals. If you were to split the variances into separate visuals next to each other you'd experience much better performance I'd imagine. I attached a screenshot of my example with very long switch statements with complex modeling in the underlying measures used in the swith. This entire page takes ~10 seconds to load.

So something like:

• Forecast Variances
• Budget Variances
• All Year Variances

Or you can elect to section based on

• Variances vs versions
• Variances vs time

Either way, I think you can improve performance via thinking about your formatting.

[v-xicai]

Hi @Anonymous ,

Az38' suggestion is great.  In addition, you may improve the performance of measures used in your SWITCH formula, such as [Actual], [Forecast], [Budget],,,etc. 

For example:

Use SELECTEDVALUE() instead of VALUES()

Use ISBLANK() instead of =Blank() check

Do not use scalar variables in SUMMARIZE()

Use variables instead of repeating measures inside the IF branch

You may refer to the tips:

Improve Power BI Performance by Optimizing DAX

A comprehensive guide to Power BI performance tuning

Best Regards,

Amy 

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]

Thanks for the feedback. Unfortunately I've already optimised the source measures and don't think I can speed them up much more. The real issue is with the consolidation of the measures in the switch

[az38]

Hi @Anonymous 

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

[Anonymous]

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

[parry2k]

@Anonymous 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.

[Anonymous]

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