Matrix visual-Show Account under last available ragged hierarchy level with Debit/Credit logic

[Rahul_Nair]

I have a Power BI Matrix visual with a ragged Balance Sheet hierarchy.

Important clarification upfront:

• The hierarchy table does NOT contain Account/Konto.
• Account/Konto exists only in a separate dimension table.
• All relationships between hierarchy, account mapping, and fact tables are already created and correct.
• The relationships made between the Dim_Account_to_Balance and DIM_Balance_Hierarchy are actually two. The relationships are two because debit position and credit position and matching with position column in DIM_Balance_Hierarchy. One is active and another is inactive at the moment.

Model setup

Hierarchy table: DIM_Balance_Hierarchy

• Columns: Level 1 … Level 7
• No Account/Konto column in this table

Account mapping table: DIM_Account_to_Balance

• Columns:
  • Account
  • DebitPosition
  • CreditPosition

Fact table:

• Columns:
  • Account
  • Amount
• Business rule:
  • Negative Amount → Debit
  • Positive Amount → Credit

Matrix Rows (in this exact order)

1. Level 1
2. Level 2
3. Level 3
4. Level 4
5. Level 5
6. Level 6
7. Level 7
8. Account

this is the current setup in rows and values section in visualization pane.

Current behavior

• Blank hierarchy levels are hidden using a mustHide measure.
• The hierarchy stops correctly at the last available non‑blank level.
• However, Account does not reliably appear under the last available hierarchy level, especially when deeper levels (Level 6 / 7) are blank.
• Account sometimes appears only when helper measures are placed in Values.

Measure logic used:

MustHide =

SWITCH (

    TRUE(),

    ISINSCOPE ( 'DIM_Balance_Hierarchy'[Level 7] ),

        ISBLANK ( SELECTEDVALUE ( 'DIM_Balance_Hierarchy'[Level 7] ) ),

    ISINSCOPE ( 'DIM_Balance_Hierarchy'[Level 6] ),

        ISBLANK ( SELECTEDVALUE ( 'DIM_Balance_Hierarchy'[Level 6] ) ),

    ISINSCOPE ( 'DIM_Balance_Hierarchy'[Level 5] ),

        ISBLANK ( SELECTEDVALUE ( 'DIM_Balance_Hierarchy'[Level 5] ) ),

    ISINSCOPE ( 'DIM_Balance_Hierarchy'[Level 4] ),

        ISBLANK ( SELECTEDVALUE ( 'DIM_Balance_Hierarchy'[Level 4] ) ),

    ISINSCOPE ( 'DIM_Balance_Hierarchy'[Level 3] ),

        ISBLANK ( SELECTEDVALUE ( 'DIM_Balance_Hierarchy'[Level 3] ) ),

    ISINSCOPE ( 'DIM_Balance_Hierarchy'[Level 2] ),

        ISBLANK ( SELECTEDVALUE ( 'DIM_Balance_Hierarchy'[Level 2] ) ),

    FALSE()

)

Headcount =

IF (

    [mustHide],

    BLANK(),

    SUMX (

        VALUES ( 'DIM_Account_to_Balance'[Account] ),

        1

    )

)

Current Level =

ISFILTERED ( 'DIM_Balance_Hierarchy'[Level 1] ) +

ISFILTERED ( 'DIM_Balance_Hierarchy'[Level 2] ) +

ISFILTERED ( 'DIM_Balance_Hierarchy'[Level 3] ) +

ISFILTERED ( 'DIM_Balance_Hierarchy'[Level 4] ) +

ISFILTERED ( 'DIM_Balance_Hierarchy'[Level 5] ) +

ISFILTERED ( 'DIM_Balance_Hierarchy'[Level 6] ) +

ISFILTERED ( 'DIM_Balance_Hierarchy'[Level 7] )

Current Level Visible =

IF (

    [mustHide],

    BLANK(),

    [Current Level]

)

Requirement

• Account must always appear under the last available hierarchy level.
• The hierarchy position under which the Account appears must be determined by fact data:
  • Negative Amount → DebitPosition
  • Positive Amount → CreditPosition
• Blank hierarchy levels must not be shown.
• The solution should work reliably in a Matrix visual.
• Relationships are already defined; the question is about Matrix behavior and DAX feasibility, not relationship design.

Question

Is it technically possible in a Power BI Matrix visual to:

• hide blank hierarchy levels and
• dynamically attach leaf nodes (Account from another dimension table) under the last available hierarchy level
• based on fact‑table Debit/Credit logic,

or is this a Matrix engine limitation that requires restructuring the hierarchy (e.g., using a derived parent‑child hierarchy that includes Account as leaf nodes)?

[v-priyankata]

Hi @Rahul_Nair 

Thank you for reaching out to the Microsoft Fabric Forum Community.

@MFelix Thanks for the inputs.

I hope the information provided by users was helpful. If you still have questions, please don't hesitate to reach out to the community.

[v-priyankata]

Hi @Rahul_Nair 

Hope everything’s going smoothly on your end. I wanted to check if the issue got sorted. if you have any other issues please reach community.

[MFelix · Accepted solution]

Hi @Rahul_Nair ,

Since you are ending with a table from another dimension you will not be able to get the expected outcome because you are making the filter to blank out at the hiearchy level (1, 2, 3,,..7) so if you don't have values at the lowest level then the calculation will never get to the account.

This is a very tricky question specially when you are dealing with Debit and Credits since the calculations will change accordingly to different rules.

Check the blog post below and video that can help you get a more assertive option for your needs.

https://www.daxpatterns.com/parent-child-hierarchies/