Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
EP1
Advocate IV
Advocate IV

Calculation Groups within Measure Calculations

Hi

 

I need some help with selectively applying calculation groups. In my data model, I have 5 types of measures:

 

Measure Type 1: measures where the calculation group should not be applied

Measure Type 2: measures where the calculation group should be applied

Measure Type 3: measures where a specific calculation item should apply, regardless of the calculation item selected

Measure Type 4: measures that are a calculation combining a measure type 1 and a measure type 3

Measure Type 5: measures that are a calculation combining a measure type 2 and a measure type 3

Measure Type 6: measures that combine two measures of type 3, where each has a different calcuation item applied

 

Up to now, I have been able to implement all measure types by using a disjointed table containing the same names as the calculation items and using the DAX TREATAS() function to selectively apply the calcuation group. However, TREATAS() comes with a significant performance cost, so I want to move away from TREATAS().

 

I have considered using a filter for SELECTEDMEASURENAME() but I suspect this would not work for measure types 3-6.

 

I'd appreciate any pointers as to what approach I can use to gain performance benefits but keep functionality.

5 REPLIES 5
bcdobbs
Super User
Super User

Have you seen this article that was in SQLBI's january newsletter:
A Technique for Optimising Calculation Group Performance – Tenfingers BI (wordpress.com)


I'm not sure it's 100% what you need but you might be able to use some of the ideas.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Thanks, I have seen it but I'll revisit it.

bcdobbs
Super User
Super User

Have you got a demo file?

Short of a direct relationship TREATAS is generally the best way to move a filter so would like to see what you're already doing in a bit more detail.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

@bcdobbs the only complaint I have with the TREATAS approach is the performance cost. In another model where I only have type 1 and type 2 measures, I successfully switched from TREATAS to a SELECTEDMEASURENAME approach with tenfold performance gains

@bcdobbs sorry, don't have a demo file. The model itself (even without the data) is confidential. I considered using a direct relationship but it doesn't look like it is possible to create a relationship on a calculation group.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.