Handle different granularities without creating relationships in Power BI (Logical Relationships Approach)

It is really common scenario in the BI data models that you have to handle the data of the different granularities when at least two tables contain different levels of information stored. For Example, you have a Revenue Table that records data at a day granularity and Budget table with data stored in the monthly granularity in your data model. If you import these tables in a data model and try to link them to a common Calender table, you cannot able to create a relationship given that the Budget table does not have a date column with data being available at the Month granularity.

REVENUE TABLE

BUDGET TABLE

CALENDAR TABLE

This scenario can be handled in the Tabular Models (Power BI/Power Pivot / SSAS Tabular) with the below approach:

However, you can’t create a physical relationship because of the many-to-many relationship scenario exists between YearMonth Column in both tables. Both tables have multiple values of YearMonth in the newly created column.

How to solve this scenario in DAX: Use Logical Relatinships

Let’s first look at the formula and then will dissect it to understand more:

In the Total Budget measure, you keep the initial IF statement that checks whether to display the data at the current selected granularity or not. If you remove this check control, the value would be propagated to other granularities (e.g., you would see the monthly Budgeted value repeated for all the days in the month) and this will give you the incorrect numbers.

What this formula is doing in DAX ( Formula Secret)

The Total Budget measure has to filter the sum of BudgetAmount using a CALCULATE function. You pass a filter argument using a FILTER function in the CALCULATE call with the list of YearMonth values that have to be included in the calculation, and a FILTER function iterates all the YearMonth values in the Budget table, keeping only those with at least a corresponding value in Calendar[YearMonth] that is active in the current filter context. ( combination of CONTAINS & VALUES function).