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!
