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

     

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