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 visua...
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,

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

Hi Anonymous 

Could you  share a simplified sample and your expected output?

 

Regards

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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. 

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi Guys,

      Is possible apply this solution for more hierarchies columns?

      For example adding one culumn

       

      Column 1Column 2Column 3Value
      aa1a31
      aa1 2
      aa2a43
      a a44
      ab1 2
      a b32
      ab1 3
      ab2b44
      • Borja204's avatar
        Borja204
        Icon for Helper II rankHelper II
        5 years ago

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

  • koen_ve's avatar
    koen_ve
    New Member
    5 years ago

    Hi,

     

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