Hierarchy inside Measure

[sparkplug93751]

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 master to establish a hierarchy ie 1 has 11, 12, and 13

All have hardkeyed plans. The masters have their own independent from a legend value.

How can I have a measure that switch between the correct master then legend after filtering ie If i have a master filter and select 2, 98 appears then if i filter again to 22, 95 appears.

https://tuprd-my.sharepoint.com/:x:/g/personal/tug67925_temple_edu/EXwVQvIKn_RDv1m9H3xbX8wBsCLJdcCbt...

[v-gizhi-msft]

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]

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

[v-gizhi-msft · Accepted solution]

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

[amitchandak]

Did not get it completely

https://community.powerbi.com/t5/Desktop/Hierarchy-of-calculated-measures/td-p/117433

https://visualbi.com/blogs/microsoft/powerbi/customizing-hierarchy-measures-dax-using-inscope/

[sparkplug93751]

The values are displaying correctly when inside a matrix but not when plotted on a line graph

[sparkplug93751]

I'm only getting blank values using isinscope