Calculation Groups Slows Performance

[EP1]

I've moved my data models away from SWITCH statements to Calculation Groups but have noticed that the Calculation Groups are often slower than the SWITCH statements. To selectively apply the calculation groups, I use a disjointed table and the TREATAS() filter expression.

In some cases the loss of performance makes the reports unusable. This is a pity, because calculation groups are so much cleaner than SWITCH statements. Am I doing something wrong - is there a way to improve the performance of calculation groups?

[parry2k]

@EP1 Thanks for sharing your finding. Eventually, it turned out to be the DAX measure. I mean TREATAS you will use in extreme use cases not in your regular Star schema scenario.

Good learning for all of us. I know that SWITCH and CG cannot be that far in timing but for sure CG is a bit slower but not drastically slower.

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[EP1 · Accepted solution]

For anyone with the same issue, I seem to have found the problem. It appears that the TREATAS() filter expression that I was using to selectively apply the calculation group logic is what was slowing down my measures.

When I used the calculation group directly, the calculation time was reduced to even less than when I used the SWITCH logic.

[bcdobbs]

I haven't had time to read in detail yet but saw this the other day which might be of use:

https://tenfingerseddy-wordpress-com.cdn.ampproject.org/c/s/tenfingerseddy.wordpress.com/2021/12/27/...

[EP1]

@bcdobbs Thanks for the link - it looks like it takes me back to switch statements... but it might be worth a try

[parry2k]

@EP1 can you analyze using DAX studio and see which measure is causing this. Sometimes on the surface, it all looks good but still I would analyze the performance using DAX Studio. How big is your dataset? Any many to many or cross filter direction set to both relationships. There are many factors.

Calculation groups are surely slow but not to the extent that it times out. It is very hard to say what is going on without looking at everything, so I would recommend analyzing performance and go from there.

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[EP1]

@parry2k , thanks! I'll do the checks and revert with my findings

[EP1]

@parry2k , all the measures are interlinked but below are the metrics for a matrix with the SWITCH logic and with calculation groups - that was the only change I made (besides for changing cross filter relationships from both to single).

SWITCH Formula

Calculation Group

The fact table of this model has about 8M rows and 27 columns. There are about 8 master data tables conected in (practically) a simple star schema. The model itself isn't very large - only about 180MB - and the measures are moderately complex.

Unfortunately, I cannot post much more information, so I guess I'll have to figure this one out on my own or revert to SWITCH logic.

[parry2k]

@EP1 I extensively use Calculation Groups and I noticed others also pointed out the performance but not to the level that the report is unusable. I'm wondering if this is something to do with the data model or the DAX you are using which is making it slower.

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[EP1]

@parry2k, the particular one I am working on has about 15 calculation items with the standard CALCUATE ( SELECTEDMEASURE () , [some filter expression]).

I then apply it to the relevant measure by using: CALCULATE([relevant_measure],TREATAS(VALUES(Table[Name]),CalculationGroup[Name]))

This measure is used in other measures' calculations. Whereas with a SWITCH statement, the tables loaded within a reasonable amount of time, the calcuation group results in a timeout