Slow Switch Performance

[worthywow1988]

Hi!

I have a switch statement to select a measure depending on the level the user wants to view. The code is similar to this:

Selected View = switch(TABLE_VIEW [Selected View],0,[Measure0],1,[Measure1],2,[Measure2],3,[Measure3],4,[Measure4],5,[Measure5])

It was running slowly (~10 seconds) so I did some experimenting. If I do the following it runs relatively quickly (~1.5 seconds)

Selected View = [Measure1]

Selected View = switch(TABLE_VIEW [Selected View],1,[Measure1])

The speed was roughly pro rata with the number of switch conditions so it seems to be the case that all the options in SWITCH are calculated regardless of if the test is true or false - is this as expected?

Is there any logic/code I can use so it only ends up calculating the selected/required measure?

Thanks!

[worthywow1988]

Hi Greg - thanks for your reply. I thought that would be the behaviour but it seems to be doing something different i.e. if [Selected View] is, say 1, then

Selected View = switch(TABLE_VIEW [Selected View],1,[Measure1],2,[Measure2],3,[Measure3],4,[Measure4],5,[Measure5])

takes much longer to run then the below

Selected View = switch(TABLE_VIEW [Selected View],1,[Measure1])

when it theory it should take the same time?!

[v-jingzhan-msft · Accepted solution]

Hi @worthywow1988 

You can put the measures into separate card visuals and use Performance Analyzer to test the run time. The run time is divided into three parts, of which the DAX query is mainly related to the calculation of the formula. You can check which part is causing the difference in running time.

Use Performance Analyzer to examine report element performance in Power BI Desktop - Power BI | Micr...

Best Regards,
Jing

[Greg_Deckler]

@worthywow1988 Put your most frequent values first. The SWITCH stops evaluating once a condition evaluates to TRUE. So, for example, let's say that 50% of your cases are 5, you would want that to be the first evaulation condition. Then if your next highest is 3, then that should be the second, and so on.