top of page
Antares Studio Games Icon_Small.png

The Antares Alien

Power BI   |  Game Development

  • Writer's pictureBrent Jones

An Alternative to Calculation Groups in Power BI

Calculation groups are great for consolidating a single metric across multiple dimensions. But they are only available if you have Tabular Editor (or some other 3rd party software). You may have hesitations about installing extensions to Power BI. But are there any alternatives available?


The folks over at SQLBI made a wonderful video on how you can achieve essentially the same thing by making some changes to your data model.


In this blog, I want to expand on this concept to see how complex we can get. In the video above, Alberto only covers time intelligence on a single dimension (the date). So I wanted to see if we can use other dimensions besides dates?

 

As a side note, I was confused between the contextual difference between "Last Period" and "Previous Period". If you were as well, here is the difference:

Assuming the last date is 12/31/2023:

  • Last Month = Dec 1 2023 to Dec 31 2023

  • Prev Month = Nov 1 2023 to Nov 30 2023

  • Last 6 Months = Jul 1 2023 to Dec 31 2023

  • Prev 6 Month = Jan 1 2023 to Jun 30 2023

 

Can we use other dimensions besides dates?

Yes. You just need to create your dimension table accordingly. Let's say, for example, you have a table for Employees. Each employee may belong to one or many groups in the company (groups, teams, cohorts, etc.).

Employee

Belongs to...

Darth Vader

Company, BI Team

Luke Skywalker

Company, BI Team

Palpatine

Company, Marketing

Han Solo

Company, Tennis Club

Darth Maul

Company, BI Team, Marketing, Tennis Club

Some employees belong to a single team, others are involved in multiple activities. All employees belong to the same company. In this example, the "Belongs to" column is our category, and we will join on the Employee name. Rearranging the table, we get this:


Employee

Group

Darth Vader

Company

Darth Vader

BI Team

Luke Skywalker

Company

Luke Skywalker

BI Team

Palpatine

Company

Palpatine

Marketing

Han Solo

Company

Han Solo

Tennis Club

Darth Maul

Company

Darth Maul

BI Team

Darth Maul

Marketing

Darth Maul

Tennis Club


Note: You can have your DB admin set up this table for you, or you can build it yourself with Power Query, assuming you have the group names associated with the employee, one way or another.





 

Having duplicate employee names will make our many to many relationship from the Employee Group table to the Fact table (spanning across the Employee table). Be sure to set the relationship filtering to BOTH (the default is single).



Now we can just bring in the "Group" column from the Employee Groups table into any visual, and it will just work. No calculation group needed!






Comentários


  • Facebook
  • Twitter
  • Instagram
bottom of page