Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
DataExplorer
Frequent Visitor

Ragged hierarchy Visualization

We have a ragged hierarchy in our organization structure. Where some departmensts of the organizations go down to 4 levels, some go down till 8. We are trying to create a Power BI report where different stats get filtered based on the level of department selected. The only visuals I could find are the hierarchy slicer custom visual and the Matrix Preview visual. However, as the number of levels differ in data, the visuals have a lot of blank space. Example data and visuals are

ExampleHierarchy.PNG

MatrixEx.PNGSlicerEx.PNG

 

Is there a way to visualize the data in a better way in Power BI?

12 REPLIES 12
wes-verh
Advocate II
Advocate II

Please consider voting to my idea to add this option to the Matrix visual:

https://community.powerbi.com/t5/Custom-Visuals-Ideas/Hide-empty-hierarchy-levels-in-Matrix-visual/i...

noerhave
Regular Visitor

Any news on this issue ?

Still not able to hide blank members in matrix visual

prasady
Microsoft Employee
Microsoft Employee

Now we can hide the blank values in Hierarchy slicer by turning off the Empty Leaves selection option.

Empty Leaves.png

Was this option removed at somepoint?  I am on the Feb 2022 client and do not see Empty leaves under the selection options on my Hierarchy Slicer.

Anonymous
Not applicable

Here is the solution!

 

Hi all,

 

I was having the same problem and I could not find anything on the web that truely solved this issue, so I developed my own modeling that would address this problem of "blanck"s appearing in visualization from ragged hierarchies.

 

Here is my solution:

 

On a high level, the solution calls for your hierarchy data table to be segmented into separate data tables to then be interconnected with a 'many to one' relationship.  These hierarchy lookup tables then will be connected to your primary data table.

 

Take the following two examples I created to validate this method

 

Solution 1 (does not work):

 

1. Create a Hierarchy table that you will use as a lookup for your primary data.  the format of this table is shown below. 

      a. Create a primary key column that refers to your last level

      b. Create several columns that describe your hierarchy i.e. L1, L2, L3, ect.

      c. Do not repeat members or level blanks before the end of your hierarchy

2. Create a many to one relationship between your hierarchy lookup table and our primary table.

3. Run a matrix visualization b dragging the values column from your primary data table into the values field and drag all your levels (L1, L2, L2 ect.) into the row section of the visualization.

 

You will notice the "blank" values problem persists.  To resolve this, try solution 2 shown in the next set of pictures

 

lCapture22.PNG l

 

 

 

Solution 2:

 

1. Segment the larger hierarchy data lookup table into 4 separate data tables. Follow the picture examples below for exact designing

      a. As you move from one hierarchy table to the next, generate new levels and tables and eliminate those observations in subsequent tables once the final levels of that observation has been reached.  Please see picture below for further detail.

2. Link your tables together following the specific relationship cardinality as shown below

3. Run a matrix visualization by dragging the values column from your primary data table into the values field and drag your different levels (L1, L2, L3 ect.) from each new hierarchy data tables into the row section of the visualization.

 

 

Following these steps will resolve the blank values you see when displaying hierarchical data that is builded off of ragged hierarchies.

 

 

Capture33.PNG

visualization input order:

Capture444.PNG

 

Capture2121.PNG

 

 

I hope this helps you and anyone struggling with this issue.

 

Best,

Eric

Hi Eric,

 

In your example there is only one child beneath each level.  If a level has multiple children the relationship becomes many-to-many and this solution does not seem to work - am I missing something?

Anonymous
Not applicable

Hi @kevinascott ,

 

Since proposing this solution, Power BI has released tools to manage regged hierarchies better. 

 

I would recommend looking into the expand/collapse formatting option for your matrix viz.  You can find this option in the format section by searching '+' in the search bar.  

 

There is also an option to hide blank rows.

 

I hope this helps.

 

Best,

Eric

v-yuezhe-msft
Microsoft Employee
Microsoft Employee

Hi @DataExplorer,

Ragged Hierarchy is currently not supported in Power BI Desktop, there is no option to hide blank values in visuals when child Hierarchy has no data in your table. You can vote up this existing idea: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/14511612-ragged-or-parent-child-h... .

In addition, for hierarchy slicer custom visual, the feature that hide blanks is in development and the feature will be released in the next version(0.8.x), for more details, please review this blog: https://azurebi.jppp.org/2016/04/02/changelog-hierarchy-slicer/.


Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hi Lydia

 

Has there been any progress or workarounds with ragged hierarchy visualisation? I have a blank value in a parent child relationship like the graph below. My data hierarchy goes 9 levels down and if I remove the child blank using the visual level filter it begins to exclude data on the prior levels. 

 

The hierarchy custom slicer allows me to remove blanks in the slicer itself but they still remain in the graph as below. 

 

 

Thank you. 

 

Picture1.png

Thanks @v-yuezhe-msft!

jmymlr
New Member

This looks like a bug in both the Matrix Preview and Hierarchy Slicer.  The original, non-preview, Matrix visual does not display the "empty" tiers.

Please try Inforiver (https://inforiver.com/) in AppSource Visual as it handles ragged hierarchies inside matrix visuals automatically as well as treat blanks and nulls as well in display properties 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.