Hide Blanks Values in Matrix - Hierarchy

[Anonymous]

Hi all, 

I want to hid the blank values. I use this metric: 

blanks = IF(MAX('Performance Indicator Hierarchy'[Performance Indicator Hierarchy Level 5 Name])<>BLANK(),1)

and put it in the visual filter level. But I want to replicate it to the remaining levels. 

I put this metric:

blanks = IF(MAX('Performance Indicator Hierarchy'[Performance Indicator Hierarchy Level 5 Name])<>BLANK(),1) &&
        IF(MAX('Performance Indicator Hierarchy'[Performance Indicator Hierarchy Level 6 Name])<>BLANK(),1) &&
        IF(MAX('Performance Indicator Hierarchy'[Performance Indicator Hierarchy Level 7 Name])<>BLANK(),1) &&
        IF(MAX('Performance Indicator Hierarchy'[Performance Indicator Hierarchy Level 8 Name])<>BLANK(),1) &&
        IF(MAX('Performance Indicator Hierarchy'[Performance Indicator Hierarchy Level 9 Name])<>BLANK(),1) &&
        IF(MAX('Performance Indicator Hierarchy'[Performance Indicator Hierarchy Level 10 Name])<>BLANK(),1)

but it didn't work. Any suggestion ?

Kind regards.

[wes-verh]

Please consider voting to my idea to add this option to the Matrix visual:

https://community.powerbi.com/t5/Custom-Visuals-Ideas/Hide-empty-hierarchy-levels-in-Matrix-visual/i...

[v-cherch-msft]

Hi @Anonymous 

You may try below measure:

blanks =
IF (
    MAX ( 'Performance Indicator Hierarchy'[Performance Indicator Hierarchy Level 5 Name] )
        <> BLANK ()
        && MAX ( 'Performance Indicator Hierarchy'[Performance Indicator Hierarchy Level 6 Name] )
            <> BLANK ()
        && MAX ( 'Performance Indicator Hierarchy'[Performance Indicator Hierarchy Level 7 Name] )
            <> BLANK (),
    1
)

Regards,

Cherie

[Anonymous]

Hi @v-cherch-msft 

Thanks for your solution, it's works but there are fields of certain levels of the hierarchy that disappear me :\. 

Once again, thanks! 

[v-cherch-msft]

Hi @Anonymous 

Could you explain more about your expected output?I cannot fully understand it.

blanks =
IF (
    MAX ( 'Performance Indicator Hierarchy'[Performance Indicator Hierarchy Level 5 Name] )
        <> BLANK ()
        || MAX ( 'Performance Indicator Hierarchy'[Performance Indicator Hierarchy Level 6 Name] )
            <> BLANK ()
        || MAX ( 'Performance Indicator Hierarchy'[Performance Indicator Hierarchy Level 7 Name] )
            <> BLANK (),
    1
)

Regards,

Cherie

[Anonymous]

Hi @v-cherch-msft ,

It's a flattened hierarchy, for example without measure I get this: 


but when I put the measure I get this:


The values ​​change, and brand marketing, for example, also disappears.

If you need more information, please tell me and thank you very much to try help me.

Kind regards. 

[v-cherch-msft]

Hi @Anonymous 

Could you  share a simplified sample and your expected output?

Regards

[Anonymous]

Hi @v-cherch-msft 

I want to apply the same method that exists in the analysis service, the option "hide blank members":

I just want to remove the blanks: 
and if apply the metric, as you can compare, the brand marketing record (for example) disappears, and the values ​​change. (Brand Marketing: -499.39 to -0.89)


I tried to make a replace values ​​for null, but i didn't succeed. 

Kind regards. 

[koen_ve]

Hi,

I have te same problem, how did you fix this?

[v-cherch-msft · Accepted solution]

Hi @Anonymous 

You may try to create a measure to get the correct total value.For example:

Measure = 
IF (
    HASONEVALUE ( Table3[Column2] )
        && MAX ( Table3[Column2] ) <> BLANK (),
    SUM ( Table3[Value] ),
    CALCULATE ( SUM ( Table3[Value] ), ALL ( Table3[Column2] ) )
)

Regards,

[Anonymous]

Hi Guys,

Is possible apply this solution for more hierarchies columns?

For example adding one culumn

Column 1	Column 2	Column 3	Value
a	a1	a3	1
a	a1		2
a	a2	a4	3
a		a4	4
a	b1		2
a		b3	2
a	b1		3
a	b2	b4	4

[Borja204]

Could anyone answer this? I have the same problem, the blank spaces are sometimes in the middle of the hierarchy.. 😕