Understanding the optimization of SWITCH

[@stevecoleman9522]

This video and associated article are very enlightening Marco. Thank you. The Formula Engine behavior can be a mystery, but videos like this shed light on how the FE works.

[@azajoey1]

I actually used index / id based switch in my recent project. as the model grew, one visual started to run for 30 seconds. I thought integer based switch will be a good solution. then i tried using the same descriptive column which is present in the slicer and become 3 seconds, and ever since then with further logic and complexity added, remained with same performance. obviously my choice was simply a brute force try-catch, but still! amazing! thanks for the deep-dive

[@craniumnr]

Nice video, but I have some questions in my mind: 1- What if we are using live connection? 2- What if we have filters on the visual we have the measure and not on the slicer? 3- What if we have more than 1 slicer? 4- What if we have filters within the measures?

[@Bharath_PBI]

Thank you master, feel like watching your video after a long time.

[@johansantacruz6464]

Thanks for this video! It's clear and useful for DAX devs.

[@michaelmuell]

Thanks so much for this! ❤ A dashboard suddenly had incredibly long (>30 sec) load times after I made the pages dynamic using SWITCH. I couldn't figure out why. In the end, removing a page filter solved the problem and decreased the load time to 2 sec.

[@louism.4980]

Very technical, thank you for sharing your knowledge! :)

[@MuscleMUSEums]

thank you very much, amazingly explained!

[@fly2sky99]

Thanks for great video. Interesting to know what's happending inside the engine.

[@omarbn92]

Thank you for the video, will row level security on measure option slicer impact performace of switch ?

[@SQLBI]

It shouldn't.

[@Guiburgueir4]

Hi Marco, What is your opinion on the new "Field Parameters" slicers compared to this? I not too knowledgeable about the query plan but as far I checked it looks simpler.

[@SQLBI]

For this specific use case Field Parameters are a better and faster solution. However, the article is about SWITCH optimization, and this is a very simple use case. Moreover, Field Parameters works only for Power BI, not in other client (not supported for Analyze in Excel, for example).

[@th3n0rthsid3]

Thanks for asking the question, it was one of the first questions to come to my mind as well :)

[@tejasbrahmbatt5344]

Would be interesting to see if the fields parameter generates more optimal query plan than switch

[@SQLBI]

Yes, fields parameter are usually better because they change the query generated by Power BI, making the entire process more efficient. More details here: www.sqlbi.com/articles/fields-parameters-in-power-bi/

[@noahhadro8213]

I have a measure that looks like this..... var _Selected = Selectedvalue(DynamicMetric[Metric],"Sales") RETURN Switch(_Selected, "Sales", Sum(FactSales[Sales]), "Cost", Sum(FactSales[Cost]), "Profit", Sum(FactSales[Profit])). When I run this against my relatively large model with Dimensions Product, Seller, and Customer it takes 7 seconds. when "Cost" is selected. But when I swap out this measure for just the "Cost" measure... Sum(FactSales[Cost]) it takes 3 seconds. The only difference in dax studio that I can see if the A Callbackid is generated when using the "switch Measure". Note that the switch selected value is directly what I am filtering on per your videos. Can Switch really kill performance this bad?

[@GaryMillerUK]

Third of the way through and he's barely mentioned SWITCH yet. I don't have the time for this when I'm working. Does the average person need to see all this under the hood stuff? Don't they just want to speed up their queries? I'm sure this is fascinating if you have the time to study it deeply. Maybe I'm not in the intended audience.

[@SQLBI]

This is an advanced article+video that explains how the optimization works. You could quickly scan the related article to see the differences in code in a few seconds. You can find the link in the video description.