Power BI: Model is not working as expected

[Taher28]

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 exists only at Level 3 (Level 1–3 columns)
Expected: In a single Matrix visual, I should be able to place the hierarchy (Level 1–4) on rows and show:

Actual Amount (USD)
Budget 2026 Both measures should filter correctly when I select any level (Level1/Level2/Level3/Level4) via slicers or drilldown.
Actual result: After normalizing into dimension tables and fact tables, the model still behaves incorrectly:

Budget does not filter correctly when Level 4 is on rows (Budget repeats or behaves like “not filtered” in some contexts).
Actual amounts also filter inconsistently in some scenarios (some hierarchy combinations disappear when measures are added).
I need help identifying the exact modeling issue and the best production approach when Budget exists only up to Level 3 but reporting needs to work down to Level 4.

What I’m looking for in the answer

Exact explanation of why filter context is not reaching FactBudget at Level 4
Recommended final schema (star/snowflake/bridge) with relationship directions and cardinality
Any required DAX patterns (example measure) that are production-safe
Guidance on handling Budget at Level 3 while reporting down to Level 4 in the same matrix
Thanks you.

[v-ssriganesh]

Hello @Taher28,

We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.

Thank you.

[v-ssriganesh]

Hi @Taher28,

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to @oussamahaimoud & @pcoley for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.

Thank you for being part of the Microsoft Fabric Community.

[oussamahaimoud · Accepted solution]

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]

@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.