Forum Discussion

mgtaylor33's avatar
mgtaylor33
Frequent Visitor
1 year ago
Solved

Why my Composite model won't flow to the detail Direct Query table

Hi, My issue is, I have a Direct Query Fact table, hold a date key, branch Key and a total sales metric. This in then linked to a Branch Dimension in Dual storage and a Dim_Calendar table , also i...
Need Help
  • v-karpurapud's avatar
    v-karpurapud
    1 year ago

    Hi mgtaylor33 


    The current model setup has issues due to multiple aggregation tables (Branch_AGG and Class_4_AGG) that cover different time periods, along with data source filtering to manage fallback behavior. Active relationships between Dim_Calendar and both AGG tables are likely causing filter propagation issues, stopping Power BI from properly switching between aggregated and DirectQuery tables.

     

    To fix this, set all relationships from the calendar dimension to the AGG tables as inactive. This way, filters will only pass through the Cold Fact Table. Make sure each AGG table has correct aggregation mappings, since missing summarizations will prevent queries from working. Set dimension tables like calendar, branch, and class to Dual storage mode so they function with both Import and DirectQuery sources.

     

    Also, use slicers and filters only on fields present in all data layers. Using fields found only in a single AGG table (like CLASS_4_ID) will block fallback. Instead of filtering the Cold Fact Table at the source, keep the full dataset in the DirectQuery table and let Power BI decide which layer to use. These changes will help maintain consistent behavior when switching between aggregated and detailed data.

    Thank you.

  • mgtaylor33's avatar
    mgtaylor33
    Frequent Visitor
    1 year ago

    Hi There, So no, still not working to what I believe how this should work.

     

    Hot data, fact table aggregated to show data from 202301  onwards 

    has a Datekey, BranchID and a Sales Value

     

    Cold Data, fact table showing non aggregated data from 202212 backwards ( 202212- 200401)

    has a Datekey, BranchID and a Sales Value

     

    I have a Dim_Date, where the Datekey for both tables are linked.

    I have a Dim_Branch table where both are linked.

     

    I have Managed aggregation between hot and cold  

    Group by for Key and Branch_ID  and SUM for the Sales_Value.

     

    ( I have also tried this without the Group By's, just the sum as well as switching off the Key relationship between Hot Fact and Dim_Date)
    What I ASSUME shoud happen is:

    I have a simple table, Holds Branch ID and the Sales Value. ( Taken from the COLD Fact table)
    I have 2 filters, 1 for Key ( The date) and the other for Branch_ID.

     

    My ASSUMPTION is, I choose any Keydate from 202301 and the table should be using the HOT_FACT table for it's data.

    I choose any keydate from 202212 backwards   and the Table SHOULD be showing data from the COLD_FACT table.

    THIS is not the case, I just get the Hot Data values appearing , once I choose later dates, the table is Blank.

     

    WHAT is it that I have wrong here?