Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Hide Blanks Values in Matrix - Hierarchy

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.

Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    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,

11 Replies

  • v-cherch-msft's avatar
    v-cherch-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        v-cherch-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        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