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.
Hi , thnks for your answers.
Let me share my current semantic model and the report to better understand the topic:
Network tree matrix component is based on 'network_hierarchy_current' table.
'network_hierarchy_current' table is in the center of my schema and 'catch' filter from the dimensions (SS, TX,..) and propagates to fact tables 'Network Issues'.
Below I will describe two scenarios sharing a screenshot where we can see 3 main components: the network tree, a matrix with network elements with issues and a table just showing 'network_hierarchy_current' content table to better ilustrate the topic.
- Scenario A: showing nulls on the tree.
In this case, nulls are shown on the tree so I can see issues for Substation and Transformer on the right matrix as well as rows for those elements on network_hierarchy_current the table:
-Scenario B: Nulls removed from the matrix by applying filter 'Feeders name' is not blank
In this case, SS and TX rown was removed from 'network_hierarchy_current' because SS and TX elements have 'lv_feeder_sk' null on data base. This removal of rows is propagated to my fact table breaking issues calculations (due to my semantic model design).
Is there any way to:
- not display null elements on the tree
- keep the semantic model in that way
- not modify the context. I mean, don´t remove rows with null column from network_hierarchy_current.
I hope is clearer now. Thanks