Understanding the optimization of SWITCH
Вставка
- Опубліковано 25 вер 2024
- The SWITCH function in DAX has been optimized over the years, and it is helpful to know what makes the optimization work best.
Article and download: sql.bi/754856?...
How to learn DAX: www.sqlbi.com/...
The definitive guide to DAX: www.sqlbi.com/...
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.
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
Very technical, thank you for sharing your knowledge! :)
Thanks for this video! It's clear and useful for DAX devs.
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?
Thank you master, feel like watching your video after a long time.
thank you very much, amazingly explained!
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.
Thanks for great video. Interesting to know what's happending inside the engine.
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?
Thank you for the video, will row level security on measure option slicer impact performace of switch ?
It shouldn't.
Would be interesting to see if the fields parameter generates more optimal query plan than switch
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/
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.
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).
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.
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.