Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
likhithar
Helper III
Helper III

No Blank rows when there is no Child Hierarchy in Matrix

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 dont 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]))

likhithar_0-1683014886304.png

 

How to achieve this scenario?

 

1 ACCEPTED SOLUTION

@Mahesh0016  
Thanks for the reply.
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]))

View solution in original post

5 REPLIES 5
Mahesh0016
Super User
Super User

@likhithar 
Measure =
IF(ISINSCOPE('Table'[Hier1]),
IF(ISINSCOPE('Table'[Hier2]),
IF(ISBLANK('Table'[Hier2]),BLANK(),SUM([value])) ,
SUM([value])),
SUM([value]))

@Mahesh0016  
Thanks for the reply.
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]))

@Mahesh0016 The Mreasure which I mentioned my Post works fine if have 2 Hierarchies . I have 4 Hierarchies which I attached in the Image,for 4 Hierarchies I need the Solution

@likhithar please Share demo data.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.