Forum Discussion
DAX - Switch - Performance
Anonymous,
Have you tried field parameters or calculation groups? Either approach would enable you to have a slicer with values such as Income Current, Income YTD, etc. When you select a value in the slicer (e.g., Income Current), it will display that measure in the visual.
I would try field parameters first since they are easier to create.
https://docs.microsoft.com/en-us/power-bi/create-reports/power-bi-field-parameters
https://www.sqlbi.com/articles/introducing-calculation-groups/
Unfortunately, the source for the report is a Tabular model, and as such the parameters didn't help. We initially started with calculation groups but they weren't helpful as well.
On the calls with Microsoft support, they mentioned that they have seen these issues happen with Calculation groups and asked us to avoid using them for complex calculations.
The latest update we have from Microsoft is that there is no issue with how the measure is written but how the tabular server is handling the switch case. We were using SQL Analysis server 2019 and apparently, this is a known issue on that server version. They have made a fix for it in the SQL AS 2022 version which is in preview mode right now and have no plans for applying the fix on the 2019 server. They did test the model in the 2022 version and the same measures seem to be working fine. So until the 2022 version is officially released we are having to search for other workarounds.
Unfortunately, the source for the report is a Tabular model, and as such the parameters didn't help. We initially started with calculation groups but they weren't helpful as well.
On the calls with Microsoft support, they mentioned that they have seen these issues happen with Calculation groups and asked us to avoid using them for complex calculations.
The latest update we have from Microsoft is that there is no issue with how the measure is written but how the tabular server is handling the switch case. We were using SQL Analysis server 2019 and apparently, this is a known issue on that server version. They have made a fix for it in the SQL AS 2022 version which is in preview mode right now and have no plans for applying the fix on the 2019 server. They did test the model in the 2022 version and the same measures seem to be working fine. So until the 2022 version is officially released we are having to search for other workarounds.