How to handle multiple fact tables with different granularity - Power BI vs. Cognos

Check list of all posts

Modeling multiple fact tables with different granularity is the key topic to resolve for all BI tools. Cognos topically resolves this problem with stitch queries along with the determinate concept. It will bring fact tables at the same granularity by aggregation, and then apply full outer join to get the combined result. 

However, Power BI does not have a stitch query concept. Power BI does not even use the SQL query to render data. Instead, Power BI uses DAX query. This document is to demonstrate two options to resolve multiple fact tables with different granularity.

Sample data:

1. Sales data at the day level

2. Other dimensions such as product

3. Simple date dimension 

4. Budget data at the month level

Solution 1: Bring budget data for day 1 for each month. 

1. Create a calculated column 

DateKey = [Year] *10000+[Month]*100 + 1

2. Create relationship between Date and Budget

3. Create two calculated measures

Budget-Sales = SUM(Budget[Budget])-SUM(Sales[Sales])

Budget vs. Sales % = DIVIDE(SUM(Budget[Budget]),SUM(Sales[Sales]))

4. Report

The whole idea is to design a fully-star-schema model for Budget vs Sales. We have transformed the different granularity to the same granularity with conformed dimensions. 

Solution 2: Create a virtual relationship between budget and date.  

1. Use VALUES or SELECTEDCOLUMNS from date to bring the date to month level granularity. It is the same concept as determinate in Cognos behind the scene.

Table = SELECTCOLUMNS(

            'Date',

            "Year", 'Date'[Year],

            "Month",'Date'[Month]

        )

2. Based on this table, we can create a calculated measure as BudgetAtMonthLevel

BudgetAtMonthLevel = CALCULATE(

    sum(Budget[Budget]),

    TREATAS( 

        SELECTCOLUMNS(

            'Date',

            "Year", 'Date'[Year],

            "Month",'Date'[Month]

        ),

        Budget[Year],

        Budget[Month]

    )

)

3.Then bring this measure to report, along with another two measures:

Budget-Sales = [BudgetAtMonthLevel]-sum(Sales[Sales])

Budget vs. Sales % = DIVIDE([BudgetAtMonthLevel],sum(Sales[Sales]))

The TREATAS function provides us an opportunity to create a virtual relationship as needed. If you come from Cognos background, you can consider this concept as the determinant. It is a compelling solution in modeling.

Solution 3: Allocate budget to the daily level  or monthly level with DAX  

The solution is to use allocation: when at the year level simply use the sum of Budget, When at the day level or monthly level use a weighted allocation. 

BudgetAtDayorMonthLevel = 

    ([Number of days in filter context ]  / [Number of days in Year in filter context ] ) *

    sum(Budget[Budget])

DEFINE
    MEASURE 'Measure'[Budget] =
        // check if it is at year level
        VAR IsAtYearLevel =
            NOT (
                ISFILTERED ( 'Date'[FullDateAlternateKey] )
                    || ISFILTERED ( 'Date'[Month] )
                    || ISFILTERED ( 'Date'[MonthNumberOfYear] )
            )
         // Budget measure       
        VAR TotalBudget =
            SUM ( 'Budget'[Budget] )
        // Number of days in Month    
        VAR NumberOfDaysInContext =
            CALCULATE (
                COUNTROWS ( 'Date' )
            )
        // Number of days in Year       
        VAR NumberOfDaysForYear =
            CALCULATE (
                CALCULATE (
                    COUNTROWS ( 'Date' ),
                    ALLEXCEPT (
                        'date',
                        'Date'[Year]
                    )
                )
            )
        // allocation %  
        VAR BudgetAllocation =
            SUMX (
                VALUES ( 'Date'[Year] ),
                NumberOfDaysInContext / NumberOfDaysForYear
            )
        // Budget      
        VAR BudgetAtYearLevel =
            CALCULATE (
                [TotalBudget],
                FILTER (
                    ALL ( 'Budget'[Year] ),
                    'Budget'[Year]
                        = MAX ( 'Date'[Year] )
                )
            )
            
        RETURN
        // Budget logic
            IF (
                IsAtYearLevel,
                TotalBudget,
                BudgetAllocation * BudgetAtYearLevel
            )
// Query            
EVALUATE
SUMMARIZECOLUMNS (
    'Date'[Year],
    'Date'[Month],
    'Date'[MonthNumberOfYear],
    "Budget", [Budget]
)
ORDER BY
    'Date'[Year],
    'Date'[MonthNumberOfYear]