DAX - Switch - Performance

[MonishaM]

Hi all,

We have recently been having issues with complicated measures using switch statements. We have individual measures for each of the time period types we have and a final wrapper measure with a nested switch to retrieve the corresponding measure based on the user selections. We are able to view the data for these measures when they are pulled into a matrix visualization individually. But when the wrapper measure is used to retrieve them, it throws memory allocation failure for the exact same selections.

On Analyzing with DAX studio we have been able to identify that the DAX engine is actually calculating all the individual measures inside the switch rather than only calculating the measure where the switch case is true.

For example, I have measures Income Current, Income YTD, Income QTD, Income FYTD, Income rolling 2 years , Income cumulative since 2008 , Income Annualised 2 years, Income Annualised since 2008 , Income index since 2008. When pulled against a hierarchy say  geography hierarchy all these measures return the correct value. No issues.

I also have a wrapper measure as below.
Income :=  Switch( Selectedvalue(time period),
                   "Current", [Income Current],

                   "YTD", [Income YTD],
                    "QTD", [Income QTD],

                    "FYTD", [Income FYTD],

                     "Rolling 2 years", [Income Rolling 2 years],

                     "Cumulative since 2008" [Income Cumulative since 2008],

                     "Annualised 2 years", [Income Annualised 2 years],

                      "Annualised since 2008", [Income Annualised since 2008],

                       "Index since 2008", [Income Index since 2008])

Now when I have selected say Annualised since 2008 on the time period and I pull in the Income measure against the same geography hierarchy it keeps loading for 10 -15 mins and then says memory allocation failure. So analyzing this in DAX studio it shows that instead of only calculating the measure [Income Annualised since 2008] it has calculated all these measures:Income Current, Income YTD, Income QTD, Income FYTD, Income rolling 2 years , Income cumulative since 2008 , Income Annualised 2 years, Income Annualised since 2008 , Income index since 2008 and then finally trying to return [Income Annualised since 2008] value.

This is a simplified example of the actual issue. In reality, the equivalent of the Income Current measure is dependent on a bunch of other measures most of which are wrapper measures themselves. And all the other Income measures like YTD, QTD, FYTD, rolling, cumulative are calculated as sumx(products()). The annualised since and Annuslised 2 years are dependent on the corresponding cumulative since and rolling measures raised to the power of another measure. So they are really complex.

We have tried a lot of different options but haven't been able to completely solve the problem. The fix that has partially solved the problem is using variables similar to those below to store the measure value when they are  valid else using blanks. This has helped for all the measures apart from the Annualised ones.                 

Income :=  
var cur = if(Selectedvalue(time period)= "Current", [Income Current], blank())

var ytd = if(Selectedvalue(time period)= "YTD", [Income YTD], blank())
RETURN Switch( Selectedvalue(time period),
                   "Current", cur,

                   "YTD", ytd,
                    "QTD", [Income QTD],

                    "FYTD", [Income FYTD],

                     "Rolling 2 years", [Income Rolling 2 years],

                     "Cumulative since 2008" [Income Cumulative since 2008],

                     "Annualised 2 years", [Income Annualised 2 years],

                      "Annualised since 2008", [Income Annualised since 2008],

                       "Index since 2008", [Income Index since 2008])

Any other ideas we could try? Have you guys ever faced this issue in any of your projects and made any fix that worked for you? I am open to trying anything at this point as I have been breaking my head for weeks on this.

ps: We have raised a support ticket with Microsoft, but it has been a slow process.

[DataInsights · Accepted solution]

@MonishaM,

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/ 

[MonishaM · Accepted solution]

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.

[DataInsights · Accepted solution]

@MonishaM,

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/