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)

oussamahaimoud's avatar
oussamahaimoud
Memorable Member
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)