How to Pass filters from Date Dimension Table to an Indirect Fact Table in Power BI

How to Pass filters from Date Dimension Table to a Next-Level Fact Table in Power BI

Scenario:
Suppose we have data Model with multiple Dimensions and Fact Tables as per below.

In the above Model, the relationships between the fact and dimension tables are as per below:

dim_Date[Date] (1:M) fact_Sales_Orders[Order_Date]

dim_Products[Product_ID] (1:M) fact_Sales_Orders[Product_ID]

dim_Customer[Customer_ID] (1:M) fact_Sales_Orders[Customer_ID]

fact_Sales_Orders[Order_Id] (1:1) fact_Sales_Returns[fk_Order_ID]

fact_Sales_Orders[Product_ID] (M:1) Bridge_Products[Product_ID]

Bridge_Products[Product_ID] (1:M) fact_Inventory[Product_ID]

Dimension Tables:
a) dim_Date : It is a Calendar date dimension table.

b) dim_Products: Its is a Products dimension table.

c) dim_Customers: It is a Customer dimension table.

Fact Tables:
a) fact_Sales_Orders : It is the main fact table contains the Sales orders details.

b) fact_Sales_Returns: It is a next level fact table connected to fact_Sales_Orders via Order_ID.

c) fact_Inventory: It is a next level fact table connected to fact_Sales_Orders via Product_ID using a Bridge Table.

Bridge Table:

Bridge_Products =

    VAR _Sales_Prod = DISTINCT(fact_Sales_Orders[Product_ID])

    VAR _Inv_Prod = DISTINCT(fact_Inventory[Product_ID])

RETURN

    DISTINCT(UNION(_Sales_Prod, _Inv_Prod))

From this Model, we can understand that dim_Date table can directly filter fact_Sales_Orders but it can't filter the other fact tables.

Now we will see how we can filter other fact tables, fact_Sales_Returns, fact_Inventory using the dim_Date table, and do the required calculations.

Now we need to fulfill the following requirements as a part of this Scenario.

1)  Filtering fact_Inventory via dim_Date:

We can pass filter from dim_Date -> fact_Sales_Orders -> fact_Inventory, and then calculate the Stock Value from fact_Inventory for selected Month from dim_Date.

Here we are calculating the Inventory Stock value for the same Month of Sales Orders.

Net Revenue = SUM(fact_Sales_Orders[Revenue])

Stock Value = SUM(fact_Inventory[Stock_Valuation])

Inventory Flag (via dim_Date) =

VAR _Stock =

CALCULATE(COUNTROWS(fact_Inventory),

  TREATAS(

        VALUES(dim_Date[Date]),

        fact_Inventory[Inventory_Date]

    )    

)

RETURN

IF (_Stock>0 ,1,0)

Stock Value (via dim_Date) =

CALCULATE( [Stock Value],

    FILTER( VALUES(fact_Inventory[Inventory_ID]),

        [Inventory Flag (via dim_Date)]=1

        )

    )

Result:

The actual [Stock Value] Measure returns the wrong result, as it ignores the date filters from the dim_Date.
It calculates the result for all Product Ids that passed from fact_Sales_Orders for the selected Month.
The correct result will return by the Stock Value (via dim_Date) measure.

2) Filtering fact_Sales_Returns via dim_Date:

We can pass filter from dim_Date -> fact_Sales_Orders -> fact_Sales_Returns, and then we calculate the Sales Returns from fact_Sales_Returns for selected Month from dim_Date.

Here we are calculating the Sales Returns value for the same Month of Sales Orders.

Sales Returns = SUM(fact_Sales_Returns[Quantity_Return])

Sales Return Flag (via dim_Date) =

VAR _Sales_Retuns =

CALCULATE(COUNTROWS(fact_Sales_Returns),

  TREATAS(

        VALUES(dim_Date[Date]),

        fact_Sales_Returns[Return_Date]

        )    

    )

RETURN

IF (_Sales_Retuns >0 ,1,0)

Sales Returns (via dim_Date) =

CALCULATE([Sales Returns],

      FILTER( VALUES(fact_Sales_Returns[Return_ID]),

        [Sales Retun Flag (via dim_Date)]=1

        )

    )

Result:

The actual [Sales Returns] Measure returns the wrong result, as it ignores the date filters from the dim_Date.
It calculates the result for all Order Ids that passed from fact_Sales_Orders for the selected Month.
The correct result will return by the Sales Returns (via dim_Date) measure.

Notes:
We can apply similar logic from other dimensions as well to enable the required filter context.

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------