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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

Removing Blanks from Matrix with Ragged Hierarchy

Hi,

 

I have a ragged employee hierarchy with different lengths that looks like the below:

 

PowerBI2020_0-1603819383392.png

 

I also have a list of Account Names and Account Owners. I also have total spend for each of the accounts.

 

I've built a matrix that sums up each of the spends with account by salesperson, and also incorporates the hierarchy information. However, since the rows of the hierarchy are different lengths, I end up with this clunky looking matrix filled with blanks because that level of the hierarchy is blank.

 

PowerBI2020_4-1603819779781.png

How do I get rid of the blanks so when I, for example, expand Hilary I see "Account G" right under her name rather than the multiple blanks. I've included my sample dashboard below, any help would be greatly appreciated, thank you!

 

Sample Dashboard: https://www.dropbox.com/s/qsqr1o8a0pbsty6/Ragged%20Hierarchy%20Dummy.pbix?dl=0

 

 

 

 

4 REPLIES 4
vishnumohanout
Frequent Visitor

Hi there,

I was able to work out a solution, for a similar issue I had, through a custom measure & some DAX. It worked for matrix visualization but creates a small performance drop. I didn't have your table details so I used some fillers instead. Make sure to use only the newly created measure, remove the old one. Good luck.

 

No_Null_Total_Spend = 
SWITCH (
    TRUE (),
    ISINSCOPE ( 'Table_Name'[Name.Level 01] )
        && ISBLANK ( SELECTEDVALUE ( 'Table_Name'[Name.Level 01] ) ), BLANK (),
    ISINSCOPE ( 'Table_Name'[Name.Level 02] )
        && ISBLANK ( SELECTEDVALUE ( 'Table_Name'[Name.Level 02] ) ), BLANK (),
    ISINSCOPE ( 'Table_Name'[Name.Level 03] )
        && ISBLANK ( SELECTEDVALUE ( 'Table_Name'[Name.Level 03] ) ), BLANK (),
    ISINSCOPE ( 'Table_Name'[Name.Level 04] )
        && ISBLANK ( SELECTEDVALUE ( 'Table_Name'[Name.Level 04] ) ), BLANK (),
    SUM ( 'Table_Name'[Total_Spend] )
)

 

Pragati11
Super User
Super User

Hi @Anonymous ,

 

As a best practices from data point of view, I suggest you clean your BLANK values. Replace Blanks with some text value.

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Anonymous
Not applicable

This would also look silly as there would be many repeat values. Was hoping for a workaround so I could just expand the first Hilary only.

 

PowerBI2020_0-1603820336842.png

 

Based on the link below, it is not possible in the default Matrix visual (with blanks in the data).
The link suggests some alternatives to consider (like no-stepped layout, custom visual ... )

 

https://radacad.com/removing-blanks-from-organizational-ragged-hierarchy-in-power-bi-matrix-visual

 

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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