Control blanks in hierarchy

Power BI
1

I have a matrix with drill down option, for some categories there are 4 levels in the drill down where as for some others only 2 levels.

image

For the ones with only 2 levels , for eg in this case Write Off , i dont want blank to be displayed in 3rd and 4th level, how to do this?

Sample file attached:

https://drive.google.com/file/d/1GxPiajMP6seQSnRFa7eLVlJdm9Hvri8w/view?usp=sharing

Dont want these blank rows to appear

image

I tried using inscope function, hasonevalue functions , but not able to stop the drill down if there are blanks in the subsequent levels.
When filter is applied using these functions, it is affecting the entire visual.

3

Bumping this post for more visibility.

4

Hi @Anu!

We noticed that your inquiry was left unsolved for quite some time now. We strive to answer ALL inquiries in the forum. However, if you are sure that you provided all pertinent context to your concerns and read how to use the forum more effectively and still find your question unanswered, you can check out tutorials to learn this yourself as your membership also comes with relevant resources that may help you with your Power BI education.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done. Thank you!

5

Hi,

I can give you the way to look for the desire result you want to achieve you can use below dax to get the desire result i put one check you need to check for all the level

Remove Blank =
if(SELECTEDVALUE(Mapping[Sub Classification 3])=BLANK(),BLANK(),sum(‘Table’[Column1]))

if you still face any difficulties just let me know i will help you out.

Thanks,
Anurag

6

Thanks for your time , but i couldnot get this? Could you ellaborate?

7

Hi ,

If i understand your problem statement you want to hide those category or subcatgeory which are blank right then you should check in your measure the same will provide you the pbix where i implemented the above dax for your reference if i am understanding your problem correctly let me know i can provide you full solution also
sample.pbix (33.4 KB)

8

image

What i want is, this highlighted row should not appear.

9

@Anu ,

The problem you’re having is the result of what’s called a ragged hierarchy. Here’s a video that discusses a simple solution to this problem:

I hope this is helpful to you.

– Brian

10

Thanks, but in my case i need to use a matrix visual itself , not slicer .

11

Hi,

Pls find the below pbix may be this is what you are looking for
sample (1).pbix (33.7 KB)

12

Could you please explain a bit?

13

Hi,

I just filter out blank through Appling filter on the visual because on your condition any sub category can be blank so we need to remove blank from visual so i used the filter option here .

Hope this explanation help you .

Thanks,
Anurag

14

But when doing so , the entire category is disappearing
image

image

15

Bumping this post for more visibility.

16

Hi @Anu - First thing, Power BI don’t consider them as Blank() but empty strings. First need to convert all Blank fields to null using Replace function in Power Query. Done in the attached sample file.

Once done, You may try below formula to get the desired results.

Hide Blank Level 3 & 4 =
SWITCH (
    TRUE (),
    ISINSCOPE ( Mapping[Sub Classification (Malls) 1] )
        && ISBLANK ( SELECTEDVALUE ( Mapping[Sub Classification (Malls) 1] ) ), BLANK (),
    ISINSCOPE ( Mapping[Sub Classification 2] )
        && ISBLANK ( SELECTEDVALUE ( Mapping[Sub Classification 2] ) ), BLANK (),
    SUM ( 'Table'[Column1] )
)

image
sample (1)_Ankit.pbix (34.6 KB)

Thanks
Ankit J

2 Likes