Falling for Calculation Groups

I was there. It was March 2019. The SQLBits conference at Manchester Grand Central. Christian Wade stood at the front of the main conference hall and announced to the Microsoft BI Community the arrival of Calculation Groups for Analysis Services. There was a lot of excitement in the room… and I was totally underwhelmed.

I’d cut my teeth on Analysis Services Tabular Models, and over time I’d grown weary of fuddy-duddy change fearing Multidimensional model developers who rather than embrace the power and flexibility of the Vertipaq engine, would just forever reference the fact that “MDX does this better” or “MDX does that better”. I’m still proud to profess that I don’t even know how to spell MDX.

This new Calculation Groups feature in Analysis Services just seemed a way to appease those Multidimensional fanatics who were still pining over the Calculated Member feature that old school Analysis Services models gave them.

Everyone seemed to be buzzing about the fact they’d now be able to manage their time intelligence calculations far more efficiently, yet I was sat there armed with Tabular Editor and a script I’d adapted, able to easily deploy all the time intelligence calcs I wanted and arrange them into an easy to follow folder structure, and all this at the touch of a button. Calculation Groups seemed to solve a problem that I didn’t have. Also, at first glance, the implementation of Calculation Groups seemed clunky and not very intuitive

Fast forward to July 2020 and Calculation Groups had now arrived in Power BI thanks to the availability of the external tools feature and the ability to now use Tabular Editor.

Even when working with large Analysis Services models, my development process had always been to prototype in Power BI first using a subset of data, able to show to stakeholders the kind of outputs they could expect before committing to start development on a full scale enterprise solution.

External tools in Power BI now gave me the opportunity to grasp that Calculation Groups nettle and see what they’re all about.

And it turns out they’re ace! They’re much more powerful than I had realised, provide a range of uses I’d never imagined and when it comes down to it, they’re really not that difficult to create.

My top 3 use cases for Calculation Groups are:

• For Time Intelligence calculations
• To toggle active/inactive relationships between tables
• To create dynamic or switching measures (as demonstrated in my recent YouTube video)

Now the 3 use cases above are certainly worthy of a detailed blog post to explain how you can implement those features. Should I write that blog? Well, I don’t have to! Because as I was planning this piece Mark Beedle from P3 Adaptive wrote exactly that blog. Go check it out!

Calculation Group use cases don’t stop there though. For some really creative examples, you need to check out the work of Kane Snyder, who uses calculation groups to help enhance the capability of the visuals in Power BI amongst other things.

The livestream he did with Reid Havens was a real eye opener for me.

So yeah, maybe it was me being fuddy-duddy and change fearing. It seems Calculation Groups are here to stay. I love them and I can’t wait to follow the increasingly creative ways the Power BI Community uses them going forward.