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.
- 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.
- 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.
- 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