Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
Hi,
I have a ragged employee hierarchy with different lengths that looks like the below:
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.
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
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] )
)
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
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.
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
User | Count |
---|---|
136 | |
70 | |
69 | |
54 | |
52 |
User | Count |
---|---|
207 | |
94 | |
64 | |
61 | |
57 |