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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.