Hello Power BI Community,
I'm facing a challenge with displaying hierarchical data in a matrix visual and am seeking assistance.
Background: I have a dimension table with five columns that represent different levels of a hierarchy: ContaTotal, ContaDad, ContaChild1, ContaChild2, and CONTA. The CONTA column is the lowest level of the hierarchy and is used to link to a fact table that contains the values for each account.
The Problem: When I try to represent this hierarchy in a matrix visual, I encounter an issue. For some accounts, not all hierarchical levels are present. For example, an account might go directly from ContaDad to CONTA, skipping the ContaChild1 and ContaChild2 levels. When I add all five columns to the matrix rows, I get blank rows with expand/collapse (+/-) buttons for the missing ContaChild1 and ContaChild2 levels. This makes the matrix confusing and hard to navigate.
Desired Outcome: I'd like the matrix to display only the relevant levels of the hierarchy. If an account doesn't have a ContaChild1 or ContaChild, I want to be able to expand ContaDad directly to see CONTA, without seeing blank rows or unnecessary expand/collapse buttons for the missing levels.
I've tried several approaches, including restructuring my data and creating calculated columns, but haven't been able to achieve the desired result.
Would anyone have suggestions or encountered a similar issue before? I appreciate any guidance or insights.
Thank you in advance for your assistance!
