Adding calculation groups to (A)AS or PBI Premium

I was playing around with calculation groups for a question. I found that adding a calculation group is not that straightforward as it should be, so I decided to blog about it to help you along.

What are calculation groups?

First let’s talk a little bit about calculation groups itself. Calculation groups is one of the most notable features for Tabular models in a long long time. In short it allows you to change measure in a certain filter context. The main use case for it is to do certain calculations over “base” measures, for example you could have the following measures (a real model usually has many more) in your model:

In most cases the business wants to see some common calculations done over these measures like YTD, QTD, YoY% etc. That means you must make a new measure for each of the calculation over the base measure. This leads to measure explosion in your model as you need to add a new measure for each variation. Very quickly this leads to not four measures but sixteen and more creating a management headache.

In come calculation groups. With calculation groups you can create a group that can apply common calculation over base measures. It’s hard to explain so let’s just dive in on how that works.

Adding calculation groups

Before we can start adding calculation groups, we need to get some prerequisites out of the way. First you need to use SQL Server 2019, Azure Analysis Services or Power BI premium datasets (with XMLA r/w turned on) . Secondly you need to make sure your compatibility level is set to at least level 1470. This can be done through SSMS and just scripting out the database and updating the compatibility level.

Now that is set, we can start adding a calculation group to the model. The best way to do this today is to use the Tabular Editor. You can do it through script as well but that is not a great experience :).

So, I connected with Tabular Editor to my AAS model and started to add a calculation group.

This creates a new calculation group and a “column”, this is the column you would use in your report on the axis or as filters.

I now rename them to “Time intelligence” and “Time Calc”:

For each value in the column I can add a row that determines what needs to happen to the “base” measure when it is selected. This object is called a “calculation item”. In my example I added YTD and QTD calculation items.

Finally, I deploy the model metadata.

Now let’s look at the magic that happens in the calculation items. This is the YTD expression I used:

=
CALCULATE ( SELECTEDMEASURE (), DATESYTD ( ‘DimDate'[Datekey] ) )

The most important part here is the DAX SELECTEDMEASURE function. That allows us to reference the current measure in context and do something with it like YTD as I am doing here. So, this will refer to the “base measure”. Each calculation item will be shown to the user and when a calculation item is selected\in context it would execute that formula. What does this look like? In this case I would add the “Time Calc” column to Columns and CalendarYear and CalendarQuarter to Rows. This would show YTD and QTD as values and each when I add Sum of Sales to Values it will execute the calculation item that is “selected” in context over the “base measure”. Like this:

Now when I swap the measure for something else like Sum of Quantity, the same calculations are now done over that “base measure”:

I can also use these calculation items as filter instead of values. Let’s say I want to do a YTD chart I can use the calculation group as filter:

Now instead of doing regular Sum of Quantity I am getting the YTD of Sum of Quantity in my chart.

Again, to me this is one the key tabular features in years. There are many more interesting scenarios for calculation groups but more on those later. If you want to read more the guys at SQLBI have a great blog post on it too.