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 t...
Need Help
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] )
)