Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.

SSAS/Power BI - Take Calculation Groups to the next level with Tabular Editor

By Benoit Fedit posted Sep 19, 2020 04:26 PM

  

Goals of this Post

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

Setting up the Environment

  • Power BI: Download here.
  • Tabular Editor: Download here.
  • SQL Server 2019 or Azure Analysis Services or power bi with external tool. (July 20 or later)

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 to maintain 100 separate measures.

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

Users have to drag in 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 table becomes very limited in term 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 group 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 group is still a real game-changer.

How do I recommend 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 measure 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 Calculation Group.
However, using the alternative method I recommend to use we can achieve it while still taking advantage of the calculation group 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 a 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 on 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 which generates PY measures for every selected measures.

// 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 measure 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 models. 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 the full advantage of the calculation groups but we 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 the Advanced Scripting here.

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

0 comments
8 views

Permalink