Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Removing Blanks from Matrix with Ragged Hierarchy

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

 

 

 

 

Need Help

4 Replies

  • Pragati11's avatar
    Pragati11
    Super User
    5 years ago

    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

  • vishnumohanout's avatar
    vishnumohanout
    Frequent Visitor
    4 years ago

    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] )
)