Martin's Blog

The 2019 release of Analysis Services (compatibility level 1500) brought about a new feature called Calculation Groups, which makes it easier to apply the same logic to multiple measures without the need to duplicate code. Each calculation group represents an entity (table) with attributes (columns) and attribute values (calculation items), and because of this implementation it can be used to deal with role-playing dimensions as well.

Before we dive into the details, this is what our tabular model looks like with multiple relationships between the Date and Internet Sales entities/tables. The active and default relationship is highlighted.

To reproduce the solution from the other blog post about role-playing dimensions, we can create a calculation group in the following manner:

Each calculation item has an Ordinal property which dictates the order in which it will appear in the table and an Expression that will define the formula that should be applied. Calculation groups themselves also have a Precedence property to control which group is applied first when multiple groups exist. What may be a little confusing here, is that a higher precedence (number) is applied first.

This is how we can define the expressions for each of our calculation items:

-- Ordinal 1
Order Date := 
CALCULATE
(
    SELECTEDMEASURE()
,   USERELATIONSHIP('Internet Sales'[Order Date Key], 'Date'[Date Key])
) 

-- Ordinal 2
Due Date := 
CALCULATE
(
    SELECTEDMEASURE()
,   USERELATIONSHIP('Internet Sales'[Due Date Key], 'Date'[Date Key])
) 

-- Ordinal 3
Ship Date := 
CALCULATE
(
    SELECTEDMEASURE()
,   USERELATIONSHIP('Internet Sales'[Ship Date Key], 'Date'[Date Key])
) 

The results shown above are exactly what we expect but there’s a few important things to take into consideration:

• The SELECTEDMEASURE() function acts as a placeholder in the expression, and is simply replaced by the measure being evaluated.
• If no calculation item is selected or if multiple items are selected, none are applied and the measure will be used as if there’s no calculation group. In the case of this solution it doesn’t matter because the active relationship will be applied (and that’s the behavior we want), but this is important to remember in other scenarios where an explicit default may be required.
• Calculation groups are applied to all measure references unless explicitly excluded by using something like the ISSELECTEDMEASURE() function. This is super important because you may not want all measures to be subjected to the expressions inside your calculation groups, or you may not want the expressions from all your calculation groups to be applied to a measure reference.
• Measures expressions with multiple measure references creates more complexity. Be careful when doing that and make sure you understand the behavior of calculations groups fully before using it everywhere. Read this article (and others in the series) by SQLBI before jumping into calculation groups too quickly!

What about drill-through results? Can we use calculation groups to simplify that?

I hate to the the bearer of bad news…but because of the fact that the drill-through queries are not connected to the originating measure expression in any way, you still have to jump through a few hoops in order to return the correct drill-through results. Implement a similar method to what I describe here to make that work correctly.

Want to download the Visual Studio project that shows the solution? Get it from my GitHub repo.