Forum Discussion
Avoid blanks on a hierarchy without modifying context
Think of the hierarchy as a single row made up of multiple columns, just like how it appears in a table, not as separate rows the way a matrix visual displays it. Because of that, if you filter out a blank in one column, you’re not just hiding that one value. You’re effectively removing the entire row. So any other columns that also have blanks on those same rows will disappear too, since those rows no longer exist in the data after filtering.
If my understanding is correct, I don't think what you want is possible with measures alone. You'll need to create a new table that skips the blank hierarchy and move the next level to where the blank is. Attached is a sample solution which is of course based on a very simple data - it can get complex with a hierarchy that involves multiple tables.
Think of the hierarchy as a single row made up of multiple columns, just like how it appears in a table, not as separate rows the way a matrix visual displays it. Because of that, if you filter out a blank in one column, you’re not just hiding that one value. You’re effectively removing the entire row. So any other columns that also have blanks on those same rows will disappear too, since those rows no longer exist in the data after filtering.
If my understanding is correct, I don't think what you want is possible with measures alone. You'll need to create a new table that skips the blank hierarchy and move the next level to where the blank is. Attached is a sample solution which is of course based on a very simple data - it can get complex with a hierarchy that involves multiple tables.