No Blank rows when there is no Child Hierarchy in Matrix

Power BI
1

Hello All,

     I have Multiple Hierarchies. For few we have Dimension at every level in Hierarchy. For few Some Low Hierarchy is Blank.

If the Low Hierarchy is Blank ,I don’t want to see the Blank row.

I implemented the solution using ISINSCOPE DAX for 2 Hierarchies, If there were more than How to do that??
Measure =
IF(ISINSCOPE(‘Table’[Hier1]),
IF(ISINSCOPE(‘Table’[Hier2]),
IF(MAX(‘Table’[Hier2])=BLANK(),BLANK(),SUM([value])) ,
SUM([value])),
SUM([value]))

image
image388×589 6.72 KB

How to achieve this scenario?

TIA

2

Bumping this post for more visibility from our experts and users.

3

If you don’t want to see blanks in [Hier2] column then select your Matrix visual and go to filters pane and under [Hier2] column Basic Filtering just enable Select All option and deselect (Blanks) option as shown in the image below.

image
image242×757 8.59 KB

4

Hi @likhitha - You can refer to below post and also can search on Ragged Heirarchies and how to handle them.

Regards,
Ankit J

5

Hello @likhitha

Did the responses above help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark the answer as the SOLUTION by clicking the three dots beside Reply and then tick the check box beside SOLUTION

Thank you

6

Hi @likhitha

Due to inactivity, a response on this post has been tagged as “Solution”.

If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

7

I got the solution with the following DAX
Sales =

SWITCH(TRUE(),

ISINSCOPE(Sheet1[Hie 4])&&SELECTEDVALUE(Sheet1[Hie 4])=BLANK(),BLANK(),

ISINSCOPE(Sheet1[Hie 3])&&SELECTEDVALUE(Sheet1[Hie 3])=BLANK(),BLANK(),

ISINSCOPE(Sheet1[Hie 2])&&SELECTEDVALUE(Sheet1[Hie 2])=BLANK(),BLANK(),

ISINSCOPE(Sheet1[Hie 1])&&SELECTEDVALUE(Sheet1[Hie 1])=BLANK(),BLANK(),SUM(Sheet1[Sales]))

Thanks for the reply.

With Regards,
Likhitha