Forum Discussion

sparkplug93751's avatar
sparkplug93751
Helper II
6 years ago
Solved

Hierarchy inside Measure

Hello,   See attached example data   My data has a planned number of people (planned column) per category   There's 2 categories - the master column and legend where each legend fall beneath a ...
Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    Do you want to show this measure in matrix visual?

    If so, please try this measure:

    Measure = 
IF (
    CALCULATE ( DISTINCTCOUNT ( 'Table'[Inside] ), ALLSELECTED ( 'Table' ) )
        <> CALCULATE ( DISTINCTCOUNT ( 'Table'[Inside] ), ALL ( 'Table' ) ),
    IF (
        ISINSCOPE ( 'Table'[Inside] ),
        BLANK (),
        CALCULATE (
            MAXX ( ALLSELECTED ( 'Table' ), 'Table'[Plan] ),
            FILTER ( 'Table', 'Table'[Master] = SELECTEDVALUE ( 'Table'[Master] ) )
        )
    ),
    CALCULATE (
        MAX ( 'Table'[Plan] ),
        FILTER (
            'Table',
            'Table'[Master] = SELECTEDVALUE ( 'Table'[Master] )
                && 'Table'[Inside] = SELECTEDVALUE ( 'Table'[Inside] )
        )
    )
)

    When only selecting master in slicer, it shows:

    When both selecting master and inside in slicers, it shows:

    Here is my changed pbix file:

    pbix 

     

    Best Regards,

    Giotto Zhi

     

v-gizhi-msft's avatar
v-gizhi-msft
Community Support
6 years ago

Hi,

 

Please try this measure:

Measure = 
IF (
    SELECTEDVALUE ( 'Table'[Inside] ) = BLANK (),
    CALCULATE (
        MAXX ( ALLSELECTED ( 'Table' ), 'Table'[Plan] ),
        FILTER ( 'Table', 'Table'[Master] = SELECTEDVALUE ( 'Table'[Master] ) )
    ),
    CALCULATE (
        MAX ( 'Table'[Plan] ),
        FILTER (
            'Table',
            'Table'[Master] = SELECTEDVALUE ( 'Table'[Master] )
                && 'Table'[Inside] = SELECTEDVALUE ( 'Table'[Inside] )
        )
    )
)

Choose this measure as a Card visual, when you only select master in slicer, it shows:

When you continue to select Inside in slicer, it shows:

Here is my test pbix file:

pbix 

Hope this helps.

 

Best Regards,

Giotto Zhi

 

  • sparkplug93751's avatar
    sparkplug93751
    Helper II
    6 years ago

    Can this be applied to a measure rather than a card?

    • v-gizhi-msft's avatar
      v-gizhi-msft
      Community Support
      6 years ago

      Hi,

       

      Do you want to show this measure in matrix visual?

      If so, please try this measure:

      Measure = 
IF (
    CALCULATE ( DISTINCTCOUNT ( 'Table'[Inside] ), ALLSELECTED ( 'Table' ) )
        <> CALCULATE ( DISTINCTCOUNT ( 'Table'[Inside] ), ALL ( 'Table' ) ),
    IF (
        ISINSCOPE ( 'Table'[Inside] ),
        BLANK (),
        CALCULATE (
            MAXX ( ALLSELECTED ( 'Table' ), 'Table'[Plan] ),
            FILTER ( 'Table', 'Table'[Master] = SELECTEDVALUE ( 'Table'[Master] ) )
        )
    ),
    CALCULATE (
        MAX ( 'Table'[Plan] ),
        FILTER (
            'Table',
            'Table'[Master] = SELECTEDVALUE ( 'Table'[Master] )
                && 'Table'[Inside] = SELECTEDVALUE ( 'Table'[Inside] )
        )
    )
)

      When only selecting master in slicer, it shows:

      When both selecting master and inside in slicers, it shows:

      Here is my changed pbix file:

      pbix 

       

      Best Regards,

      Giotto Zhi