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.
This scenario can be handled in the Tabular Models (Power BI/Power Pivot / SSAS Tabular) with the below approach:
Approach – Without Physical Relationships & Using Logical Relationships
An approach to this is that writing a DAX Measure that simulates the relationships between Calendar Table and Budget Table at the monthly level.
This time you create YearMonth Column in both Calendar & Budget Column.
Budget[YearMonth] = Budget[Year] * 100 + Budget[Month] Calendar[YearMonth] = Calendar[Year] * 100 + Calendar[MonthNumber]
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:
[Total Budget] := IF ( NOT ( ISFILTERED ( Calendar[Date] ) ), CALCULATE ( SUM ( Budget[BudgetAmount] ), FILTER ( ALL ( Budget[YearMonth] ), CONTAINS ( VALUES ( Calendar[YearMonth] ), Calendar[YearMonth], Budget[YearMonth] ) ) ) )
So what we have done is applying a logical relationship through columns (YearMonth) that are not keys in the both tables (Budget & Calendar) to filter the measure.
Budget is the target table and Calendar is the look up table in this scenario.
[Measure] := IF ( NOT ( ISFILTERED ( Calendar[Date] ) ), CALCULATE ( <target_measure>, FILTER ( ALL ( <target_granularity_column> ), CONTAINS ( VALUES ( <lookup_granularity_column> ), <lookup_granularity_column>, <target_granularity_column> ) ) ) )
In case, if you have many logical relationships exist between both tables, you pass one FILTER argument to CALCULATE for each logical relationship as show below.
[ManyLogicalRelationshipsMeasure] := CALCULATE ( <target_measure>, FILTER ( ALL ( <target_granularity_column_1> ), CONTAINS ( VALUES ( <lookup_granularity_column_1> ), <lookup_granularity_column_1>, <target_granularity_column_1> ) ), ... FILTER ( ALL ( <target_granularity_column_N> ), CONTAINS ( VALUES ( <lookup_granularity_column_N > ), <lookup_granularity_column_N >, <target_granularity_column_N > ) ) )
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).
You can download the sample file from here.