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


Granularity

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

RevenueTable

BUDGET TABLE

BudgetTable

CALENDAR TABLE

Calendar

 

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

Data Model

Logical Relationships

 

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.

Template Version

[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).

Final Results:

final.png

 

You can download the sample file from here.

 

Previous How to handle the SQL CASE logic in DAX
Next The internal behavior of Calculated Columns, Row Contexts and the EARLIER() Function in Power BI

No Comment

Leave a Reply