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.

5 Replies to “Adding calculation groups to (A)AS or PBI Premium

  1. Hi Kasper,

    I’ve worked with the Calculation Groups and this is a great addition. We are looking to select more than 1 Calculation Item in a Slicer (Filter) but that seems to fail with a error when using a Matrix.
    I noticed this appears to be exected (documented) behaviour, but for some reason it works fine on a Graph.
    Today I installed CU4 for SQl Server 2019, and now it looks like it is working for a Matrix as well.
    However I didn’t find this was covered in any of the fixes in CU4.
    Do you have any more info on this?

    1. Hi Frans,

      Looks like they fixed it but never documented it. I got this back “This error supposed to help with run-away queries in MDX but it was hurting valid queries as well.
      We improved MDX performance and got rid of this error entirely as performance improvements eliminated need for this error”.

      Thanks,
      Kasper

  2. Hi,
    Nice post and I have tried to get this working following your article but when I deploy to my AS server it won’t process with the message.
    The database compatibility level of 1500 is below the minimal compatability level of 2147483647 needed for [model ‘Distribution’].[[table ‘Time Intelligence’].[partition ‘Partition’]::[Type]]. (Microsoft.AnalysisServices.AppLocal.Tabular)

    My server is currently on 15.0.34.14 with CU4 applied. If I delete the Calculation groups folder in Tabular Editor it works fine again once deployed.

    Not sure what I am doing wrong.

  3. Hi Kasper,

    Is this possible in Power BI Desktop? I tried connecting tabular editor with PBI dataset (premium capacity) using xmla end point but calculation groups are disabled?

    Also, on a more architectural question, for enterprise BI, do you begin development in Visual studio or Power BI desktop to create datasets to be deployed onto PBI Service – premium capacity.

    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.