How can I reduce blank rows in a hierarchical setup in my dashboard?

[sruthi_g]

I have a hierarchy like setup in my dashaboard. The data for these levels is split level wise in PowerQuery. I'm using a SQL Server Source which gives an output like this- A-Level1|B-UnderA|C-UnderB and so on. I use recursion to achieve this path dynamically for each row. The number of levels is different for all rows. So for a row with 3 levels- the 4th level on would be blank. But a row with 4 levels would have data there too. Problem is that when I convert this on to a dashboard, it shows a lot of blanks along with the data which isn't ideal for the user experience. Any suggestion how to handle this?

Here, facilities doesn't have data for more than a level. But since the other rows have a data with maximum of 8 levels, all these levels show up blank. I don't want this to be visible in the dashboard. Only levels that have data should be visible.

[v-tangjie-msft · Accepted solution]

Hi @sruthi_g ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data.  

(2) We can create a measure. 

Flag = SWITCH(TRUE(),
ISINSCOPE('Table'[Column3]) &&MAX('Table'[Column3])=BLANK() ,0,
ISINSCOPE('Table'[Column2]) && MAX('Table'[Column2])=BLANK(),0,1)

(3) Place [Flag=1] on visual object screening and then the result is as follows.

This post also describes another solution that you can refer to.

Parent-child hierarchies – DAX Patterns

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.