Now it’s been a year since the game-changer new calculation groups feature has been released and I just thought that I still haven’t written a post on it.

Goals of this Post

• Use Calculation Groups
• Use Tabular Editor Advanced Scripting
• Speed up SSAS/PowerBI model development
• Create reusbale script

Setting up the Environment

• Power BI: Download here.
• Tabular Editor: Download here.
• SQL Server 2019 and later Analysis Services or Azure Analysis Services.

At the time of writing this post, Calculation Group Feature has not yet been released in PowerBI Non-Premium but it’s definitely in their backlog…

Calculation Groups

I’m not going to repeat all the technical details that Microsoft has already nicely written here.

The goal of this post is to show another approach on how to use the calculation groups and demonstrate why you may prefer to use this alternative approach instead of the one suggested in the Microsoft article.

First let’s weigh the Pros and Cons of the Calculation Groups.

As Microsft says, Calculation groups address an issue in complex models where there can be a proliferation of redundant measures using the same calculations – such as Sales PY, Cost PY, Profit PY, Sales YTD, Profit YTD and so on.

So the main benefit of using Calculation Groups is to reduce the number of measures to create and to maintain. 

If we have 20 measures in our model and each one of them has 5-time intelligence calculations we end up having to create and maintain 100 separate measures.

However, one of the disadvantages of using Calculation Groups is that it is not flexible.

Users have to drag in the column the Time Intelligence and use the Time Calculations slicer to select the time calculations they want to see on their dashboards.
So building reports, dashboards or pivot tables becomes very limited in terms of displaying, sorting and formating the measures as we wish.

For example, many users want to see a report with Cost, Cost PY, Sales, Sales PY, Profit, Profit PY and Profit YOY. Truth is that now we cannot achieve that with the calculation groups. (Unless we use the below alternative method)

So let’s create a PBI matrix using the Calculation groups feature. 
What’s wrong with it?

• We cannot order the measures as we wish
• We cannot show the YOY profit only without showing cost and sales
• We cannot apply conditional formatting only on YOY
• Users have to drag item calculation in column might be confusing
• Many users especially in finance, prefer to use the pivot table in Excel instead of PBI so calculation groups become even less flexible

Hold on is calculation groups that bad?

Of course not in many scenarios using the recommended Calculation Groups approach will be perfectly fine. And even with the different approach that I’m going to show Calculation groups is still a real game-changer.

How do I recommand to use the Calculation Groups?

The way I like to use Calculation Groups doesn’t help to reduce the number of measures but it still drastically speeds up the time of creating 100 measures and reduces the burden to maintain them.

Here is the visualisation I need to have:
– YOY for profit only
– Conditional formating on profit
– Custom order

We cannot build such a matrix using the recommended approach of the Calculation Groups.
However, using the alternative method I recommend to use we can achieve it while still taking advantage of the calculation groups capability to speed up our development and ease any future changes.

Create our Calculation Items

So here is how we can create a PY Time Intelligence using calculation groups. All the technical details and different patterns are available here

Generate Time Intelligence Measure

As said above we need to create separate measures, however, as we use the calculation groups feature it helps to speed up the development time as we no longer need to copy/paste long the time intelligence formulas.
Also maintaining existing measures becomes very easy as whenever we need to change the logic of the formula we’ll make the change in only one place.
Additionally, all the separate measures automatically inherit the format properties of the calculation item so we can maintain the format in only one place as well.

Sales PY = CALCULATE ( [Sales], 'Time Intelligence'[Time Calculation] = "PY" )

Generate Multiple Measures

Using the Advanced Script of Tabular Editor we can create multiple measures at once and thus speeding up the development time even more.
The code below creates a custom action that generates PY measures for every selected measure.

// Creates a PY measure for every selected measure.
foreach(var m in Selected.Measures) {
    m.Table.AddMeasure(
    m.Name + " PY",                                       // Name
    "Calculate(" + m.DaxObjectName + ", 'Time Intelligence'[Time Calculation]=\"PY\")",    
        m.DisplayFolder                                        // Display Folder
    );
}

Once the code compiles we can save the script and name it accordingly.

Now we just need to select the measures for which we want to generate PY time intelligence measure.

Below I generate six measures in only two clicks. Now imagine if we have 50 measures to generate, it would still take us only two clicks!

Reuse Custom Actions

Another great thing is that we can even reuse our custom action in any model. No need to recode or copy/paste our script, all we need to do is to import our existing custom action:

Custom Actions are stored in the CustomActions.json file within %AppData%\Local\TabularEditor.

By combining the Calculation Groups feature with the Advanced Scripting of tabular editor we not only take full advantage of the calculation groups but also keep the flexibility of having separate measures.

So to recap this approach allows us to:

• Drastically reduce time to develop our model
• Take the full advantage of the calculation groups such as dynamic formatting, dynamic currency conversion…
• Keep all the flexibility of having separate measures
• If we need to change the logic of our measures we change it on only one place: the calculation item

You can find more information about Advanced Scripting here.

I hope you found this post useful and please let me know if you like or don’t like this method.