Calculation Groups are Coming to DAX - Excelerator BI

Calculation Groups are Coming to DAX

I woke up Saturday morning to a lot of chatter and blog articles about an announcement that Microsoft made at SQL Bits.  Microsoft is adding “Calculation Groups” to the DAX language. There are a number of blogs already that talk about the announcement, including Chris Webb and The Italians, but I wanted to give my own spin on this from a business DAX user’s perspective.

What is the Problem that Calculation Groups will solve?

Currently in the DAX language, you have 3 choices to visualise your data.

  1. Drag a column from a table to a visual and use one of the summarisation features to aggregate your data (SUM, COUNT, AVERAGE etc).
    • This is quick and easy, but is very limited in how it can be used in more complex models.  You also can’t drag columns like this with Analyze in Excel.
  2. Use the Quick Measures wizard to automatically write a measure for you.
    • This can be useful if you don’t know any/much DAX and have a simple need (such as reporting on YTD), but it is really just a path leading to the next point.
  3. Write your own measures using DAX.

Most value adding Power BI reports will contain many measures in the data model. More complex data models can have many measures, such as:

  • Total Sales $,
  • Total Cases,
  • Total Units,
  • Total Invoices,
  • Average Items per Invoice
  • Average Sales $ per Invoice

The list can go on and on for literally hundreds of these “base measures”.

And then there is Time Intelligence

Another common use case is to want to see all of the base measures represented over time.  Currently this is deployed in DAX via more measures, such as:

  • Total Sales $ YTD
  • Total Sales $ MTD
  • Total Sales $ Moving Annual Total
  • Total Sales $ Rolling 90 Days
  • Total Sales $ LY
  • Change in Total Sales $ vs LY
  • % Change in Total Sales $ vs LY

The list can go on and on for 10’s of these “time series variations” to the base measures, and this list is just the [Total Sales $] base measure.  Then you have to write these variations for all of the other base measures.  As you can probably see by now, the total number of measures will be B x T where B = count of base measures and T = count of time intelligence variations.

What are Calculation Groups in DAX?

Calculation groups are a new way to solve the above proliferation of measures.  When calculation groups come to Power BI Desktop, you will be able to

  • write the base measures you need,
  • create one or more calculation groups that manages the time intelligence representation for your measures.
  • combine the base measures with the calculation groups to create the time intelligence representations of all the base measures without the need to write each one as a separate measure.

Once created, a calculated group will “look and feel” like a regular table and column in Power BI.  See the image below.

Other Uses for Calculation Groups

It is important to point out that time intelligence will not be the only use case for calculation groups; I just think it will be the most common use case and the one that most business users will understand.  I am sure there will be a plethora of other use cases that Microsoft hasn’t even thought of as yet once the community gets hold of this capability (think what has been done with SWITCH measures!).  One use case that comes to mind for me is the ability to toggle between a base value, 000’s, and 000,000’s representations of the numbers.  The UI can already do this (it can display 29,345,234 as 29.3M) but many business users are used to seeing numbers like 29.35 where the number is in millions, without the M appended.  My assumption is that this will be possible using calculation groups.

Calculation Groups Change Everything in DAX

This is a very significant change to the DAX language.  In fact it is probably the most significant change since the birth of the language.  It will require new chapters to be written for the next release of my books and will change the way everyone learns the DAX language.

Enough All Ready, When Can I Have It?

It is hard to say.  It has been announced as being available in SQL Server Analysis Services 2019 for now.  I will certainly keep you updated when it becomes more commonly available in Power BI.

update:  moving this feature to Power BI has not been planned. You can help by voting for it here https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/37146049-support-creating-calculation-groups-in-power-bi-de

7 thoughts on “Calculation Groups are Coming to DAX”

  1. Great news!
    Writing so many measures for time analysis was really a nightmare!

    I had a way (for quick analysis) to create a measure as below, then an additional “Measures” table, and then with Matrix visual (“Measure” column as header) I could get a quick look at the data in time.

    TimeVal =
    VAR val = SUM ( Dane[Value])
    VAR PY = IF (val BLANK ();CALCULATE ( SUM ( Dane[Value] ); DATEADD ( Cal[Data]; -1; YEAR)))
    VAR PM = IF (val BLANK ();CALCULATE ( SUM ( Dane[Value] ); DATEADD ( Cal[Data]; -1; MONTH)))
    VAR Y_TD = TOTALYTD(SUM ( Dane[Value]);Cal[Data])
    VAR Y_TD_PY = CALCULATE(TOTALYTD ( SUM ( Dane[Value] ); Cal[Data] );DATEADD ( Cal[Data]; -1; YEAR ))

    VAR Y_TD_YoY = IF ( Y_TD_PY BLANK (); Y_TD – Y_TD_PY )
    VAR Y_TD_YoY_Pct = IF ( val BLANK (); FORMAT ( DIVIDE ( Y_TD_YoY; Y_TD_PY ); “# ###%” ) )
    VAR MoM = IF ( PY BLANK (); val – PM )
    VAR MoM_Pct = IF ( val BLANK (); FORMAT ( DIVIDE ( MoM; val ); “# ###%” ) )
    VAR YoY = IF ( PY BLANK (); val – PY )
    VAR YoY_Pct = IF ( val BLANK (); FORMAT ( DIVIDE ( yoy; val ); “# ###%” ) )
    RETURN SWITCH (
    SELECTEDVALUE ( ‘Measures'[Measure]);
    “Cur”; val;
    “PM”; PM;
    “MoM”; MoM;
    “MoM%”; Mom_Pct;
    “PY”; PY;
    “YoY”; YoY;
    “Yoy%”; YoY_Pct;
    “YTD”; Y_TD;
    “YTD PY”; Y_TD_PY;
    “YTD YoY”; Y_TD_YoY;
    “YTD YoY%”; Y_TD_YoY_Pct)

    Then copying this formula into new measure and replacing the i.e. “SUM (Data[Value])” with “SUM (Data[Qty] )” in formula in just five places made it possible to quickly obtain these results for a different measurement as well. The choice which measures to display was obtained by applying a filter to “Measures” with the use of a slicer.

    But of course this way has so many limitations that this message from the Power BI team is really exciting. I can’t wait!

  2. I wonder when we will be hearing about the new DAX functions CONTAINSSTRING, CONTAINSSTRINGEXACT and DISTINCTCOUNTNOBLANK? I thought SQLbits would be the place, alas not.

  3. This will be awesome. Can’t wait till it comes out. Will be a huge time saver. I spend a lot of time writing DAX measures that are only slight time and date variations of others measures with the same base.

  4. Excellent Post Matt! You gave the introduction to the great DAX feature that is coming up in your usual way of making things simple to understand. We look forward to use the feature in Power BI understanding how to do it from the next release of your book and/or enrolling to your Online training.

  5. Wow! When does this ride stop? There is so much additional content created for Power BI in the 4 years since its launch … new DAX functions, Visuals, Power Query interface and functions, M Language functions, Apps, Embedded and Templates. It’s a full-time job just keeping up to date.

    I wonder if Microsoft is aware that each time it releases one of these features I have to go through and re-edit my models to take advantage of the just-released newest thing. I’m wondering if they should re-think their release schedule to major releases one per year, while DAX and M updates remain on a monthly schedule. It’s getting to the point of there being too much to digest.

Leave a Comment

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

Scroll to Top