How to handle different granularities in Power BI
Have you ever found yourself facing the challenge of mixing data with different granularities in Power BI?
For example, picture this: you are dealing with your daily sales figures and suddenly you need to compare them with long-term yearly targets. This request comes very often from stakeholders. How would you handle this in your data model? DAX? Well like almost everything in Power BI, there is more than one solution.
In our example, we will work with the Contoso database. I added a new table to the Contoso database named “TargetSales”. The granularity of Product table is product, so I have multiple products assigned to the same brand. Therefore, the brand attribute in Product table is not unique.
Before we dive into Power BI, first let’s go back to the fundamentals (I am talking about Star Schema).
Dimensional modeling (Star Schema)
According to my two favorite books about dimensional modeling “Star Schema The Complete Reference” by Christopher Adamson and “The Data Warehouse Toolkit” by Ralph Kimball and Margy Ross, there are four steps to follow when designing a dimensional model:
Step 1: Select the business process
Step 2: Declare the grain
Step 3: Identify the dimensions
Step 4: Identify the facts
I won’t go through all the steps in this article (for more details refer to one of the books above). If you identify that the facts in step 4 have different grain from the already defined grain in step 2, then it must be separated in another fact table.
Let’s see our example: there are two fact tables (Sales and TargetSales).
Sales represents one row per product on sales transaction each day
TargetSales represents one row per brand on target sales each year
So, they have different grains and should be in separate fact tables. Okay, but how can I handle this in my data model in Power BI?
Option 1: create a new dimension table (not recommended)
The first option that might come to mind is to create another dimension table with only brand names and then create a one-to-many relationship to the TargetSales table as we see below:
Well, that might seem like a good solution at first glance but this is definitely not a good idea for many reasons. Often report builders are not the dataset designers and it can be difficult to know as a report builder which brand column to use, the brand from Product table or from Brand table? You might end up with something like this:
Left table: when using Brand from Product table, the value from Target Sales is always the same because there is no connection between Product table and TargetSales table.
Right table: the same happens for Sales table when using Brand from Brand table
Normally it is not a best practice to repeat the same attribute in multiple dimension tables. In my opinion the dataset creator should create datasets aiming for self-service BI and expecting that the business user might also want to use your dataset to build reports or analysis on top of that. That is why I don’t recommend doing it this way.
Advantages of using this option:
Easy and quick to implement
Disadvantages of using this option:
Low usability. It can create confusion which Brand column to use in the report
It can create a mess in the data model if the model gets bigger and bigger
Option 2: using DAX
The second option is to use the DAX function TREATAS to create a virtual relationship. Using this method there is no physical relationship in the model. See the DAX code below and the result in a table with the brand column from the Product table:
Granularity issue
As we can see the values from target sales are correct for each brand. However, what would happen if I had a matrix with brand and color together? What result would I get from the measure “Target Sales TreatAs”?
The target is only related to the brand column, but the filtering still happens when browsing by other columns. This is very dangerous and can lead end users to interpret wrong results!
How can the granularity issue be fixed? Well, it is possible to use some DAX to hide the result if someone browses in the wrong granularity. The idea is to only show the target values for brand and return BLANK for other columns.
Solving the granularity issue
See below the DAX code to hide the result if the granularity does not match. Credits to SQLBI for this DAX code. I will not explain the code below, since you can find a very detailed explanation from SQLBI HERE. Using this method will hide the wrong granularity and prevent the user from getting wrong results.
Advantages of using this option:
Flexibility, since we have the control to change the filter context dynamically
No complexity added to the model
Disadvantages of using this option:
The DAX code pattern should be implemented for every measure and could be error-prone
DAX measure leverages the Formula Engine (FE), which is slower than the storage engine (VertiPaq)
My favorite explanation about the DAX engines is from the book “The Definitive Guide to DAX” from Marco Russo and Alberto Ferrari. They go really deep and explain it very well.
Option 3: using the build-in many-to-many cardinality relationship
In Power BI Desktop there is also an option to use many-to-many cardinality relationship. A detailed explanation about many-to-many cardinality relationships can be found at Microsoft documentation (HERE) and a very good video from SQLBI (HERE). You can find below what you would get if you try to create a relationship when there are no unique values to create a one-to-many relationship.
I would not recommend using many-to-many cardinality, since it can significantly elevate the level of complexity in your data model, especially if you don’t really understand what it does. This could get even worse if you activate the cross filter direction to both (which is the default setting at the time of this article). Instead, I would suggest to use bridge tables, as explained in Option 4.
Option 4: using a bridge table to build a physical relationship
The last option from this article to solve the many-to-many cardinality relationship is using bridge tables - I have to say that this is my favorite option. In order to use a bridge table, first we need to create the bridge table which is going to be the distinct values from the column brand from both tables, Product and TargetSales. It is up to you how you create the bridge table, in DAX, M or in SQL. But remember the Roche’s Maxim of Data Transformation:
“Data should be transformed as far upstream as possible, and as far downstream as necessary.”
Because I am using the Contoso database I have created the bridge table in SQL as you can see below:
SELECT DISTINCT [Brand] FROM Data.Product
UNION
SELECT DISTINCT [Brand] FROM Data.TargetSales
After importing the bridge table in Power BI, the relationships are created as follows:
The bridge table has a one-to-many relationship with the brand column from both Product and TargetSales table. The cross filter direction from the Product table to the bridge table is set to both, so that the filter propagation can reach the fact table TargetSales.
How does it work?
When the user filters Brand from Product table, the filter propagates to the bridge table (because the cross filter is set to both) and reaches the fact table TargetSales (because of the one-to-many relationship between the bridge table and the fact table TargetSales).
What about the granularity issue that we discussed in Option 2?
The DAX pattern to solve the granularity issue still has to be implemented. See the DAX code below:
I will not explain the code below, since you can find a very detailed explanation from SQLBI HERE.
Advantages of using this option:
It improves usability
The physical relationship leverages the storage engine making the model faster than DAX code
Use of conformed dimensions
Disadvantages of using this option:
It increases the complexity in the model
It is worth noting that cross filter direction set to both should only be used, if you know exactly what you are doing, otherwise you might face ambiguity problems in your data model. HERE is a more detailed explanation from SQLBI.
Conclusion
In this post we have talked about different options how to handle different granularities in Power BI. You might ask yourself: “which approach should I use?” Like almost everything in Power BI the answer is: “it depends”. Each approach has its advantages and disadvantages.
I personally prefer using bridge table (Option 4) mostly to improve usability, since I don’t need to write the DAX code with TREATAS for every measure. Also, this option leverages the storage engine (VertiPaq). Regardless of the method you choose, it is important that you understand what you are doing to avoid big problems (such as ambiguity, performance, usability…).
Thanks for reading! Let me know your thoughts in the comments below.
Last updated on August 15, 2023