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 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_RDv1m9H3xbX8wBsCLJdcCbtXi6y3pnvbtmrw?e=kIH9u2

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

     

6 Replies

    • sparkplug93751's avatar
      sparkplug93751
      Helper II
      6 years ago

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

  • 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

     

      • 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