Forum Discussion

Taher28's avatar
Taher28
Frequent Visitor
3 months ago
Solved

Power BI: Model is not working as expected

Problem: I am building a Power BI semantic model (Fabric) to report Actual vs Budget across a hierarchy Level 1 → Level 2 → Level 3 → Level 4. Actuals exist at Level 4 (Level 1–4 columns) Budget e...
Data Protection
  • oussamahaimoud's avatar
    oussamahaimoud
    3 months ago

    Hello Taher28,

    Hope you are doing well!

     

    FactBudget only has Level3Key. When the matrix drills to Level 4, the filter context carries a Level4Key that has no path to FactBudget → Budget repeats or goes blank.

     

    Correct Schema (3 tables):

    DimHierarchy (L4 grain, has Level3Key column)

        │

        ├──► FactActuals (join on HierarchyKey)

        │

        └──► DimHierarchyL3 (join on Level3Key, Many:1)

                  │

                  └──► FactBudget (join on Level3Key, Many:1)

     

    All relationships single direction. No bi-directional filters.

     

    The one critical DAX fix:

    Budget 2026 =

    VAR _L3Keys = VALUES( DimHierarchy[Level3Key] )

    RETURN

        CALCULATE(

            SUM( FactBudget[BudgetAmount] ),

            TREATAS( _L3Keys, DimHierarchyL3[Level3Key] )

        )

     

    TREATAS re-anchors the filter context from L4 → L3 explicitly, bypassing relationship ambiguity.

     

    Here's some rules :

    ✅Separate L3 bridge dimension ❌Single flat fact with NULLs

    ✅TREATAS to map grain ❌RELATED() inside measure

    ✅Single-direction relationships ❌Bi-directional on any fact

     

    To sum up : Schema + TREATAS = problem solved.

     

    Hope this helps! Don't forget to mark as solution and thumbs up, that's motivate me to keep helping 🙂 

     

    Best regards,

    Oussama (Data Consultant - Expert Fabric & Power BI)

pcoley's avatar
pcoley
Super User
3 months ago

Taher28 

Your FactBudget table only has keys up to Level 3.

When the matrix visual (or any slicer) is at Level 4, the filter context includes Level 4 = SomeValue.

This filter has no matching rows in FactBudget, therefore the relationship doesn't propagate any filter:

Budget measure returns the total unfiltered Budget (or repeats the L3 value) for every L4 under that L3.

 

When you bring in both measures, sometimes the matrix visual hides rows where one measure is blank unless you explicit keep blanks at the respective viz.

 

This is a classic different granularity problem between two fact tables sharing a hierarchical dimension.