Forum Discussion

unnijoy's avatar
unnijoy
Post Prodigy
6 years ago

HC calculation

Hi amitchandak    Country Qtr Emp HC India Qtr 1 2019 Sam 234 India Qtr 1 2019 Raj 234 India Qtr 1 2019 kit 234 India Qtr 1 2019 mas 234 India Qtr 1 2019 den 234...
Need Help
v-gizhi-msft's avatar
v-gizhi-msft
Community Support
6 years ago

Hi,

 

Please try to create a seperate slicer table first:

Slicer Table = DISTINCT(SELECTCOLUMNS('Table',"Qtr",'Table'[Qtr]))

Then try this measure:

Measure = 
SWITCH (
    TRUE,
    SELECTEDVALUE ( 'Slicer Table'[Qtr] ) = "Qtr 1 2019", IF ( MAX ( 'Table'[Qtr] ) = SELECTEDVALUE ( 'Slicer Table'[Qtr] ), 1, 0 ),
    SELECTEDVALUE ( 'Slicer Table'[Qtr] ) = "Qtr 2 2019", IF ( MAX ( 'Table'[Qtr] ) = SELECTEDVALUE ( 'Slicer Table'[Qtr] ), 1, 0 ),
    DISTINCTCOUNT ( 'Slicer Table'[Qtr] )
        = CALCULATE ( COUNTROWS ( 'Slicer Table' ), ALL ( 'Slicer Table' ) ), IF (
        MAX ( 'Table'[Quarter] ) = MAXX ( ALL ( 'Table' ), 'Table'[Quarter] ),
        1,
        0
    )
)

Then apply it to the table visual by setting measure=1, when select one value in slicer, the result shows:

Here is my test pbix file:

pbix 

Hope this helps.

 

Best Regards,

Giotto

unnijoy's avatar
unnijoy
Post Prodigy
6 years ago

v-gizhi-msft ,

 

Thanks for the quick reply.

 

Here we have fixed it to only 2 quarter. Qtr 1 2019 and Qtr 2 2019.

But our data will keep on increasing. So other quarters will also come. So how can we make this dynamic. Like if we have quarter data from 2019 till date and if we select all it should show latest quarter HC or if we are selecting only a particulr quarter then it shoud show that quarter HC

 

Please help.

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

    Hi,

     

    Please try to create two columns first:

     

    Year = RIGHT(RIGHT('Table'[Qtr],6),4)
Quarter = LEFT(RIGHT('Table'[Qtr],6),1)

     

    Then try this measure:

     

    Measure = 
SWITCH (
    TRUE,
    MAX ( 'Table'[Qtr] ) = SELECTEDVALUE ( 'Slicer Table'[Qtr] ), 1,
    DISTINCTCOUNT ( 'Slicer Table'[Qtr] )
        = CALCULATE ( COUNTROWS ( 'Slicer Table' ), ALL ( 'Slicer Table' ) ), IF (
        MAX ( 'Table'[Quarter] ) = MAXX ( ALL ( 'Table' ), 'Table'[Quarter] )
            && MAX ( 'Table'[Year] ) = MAXX ( ALL ( 'Table' ), 'Table'[Year] ),
        1,
        0
    )
)

     

    And this measure can adapt any new data by Qtr.

    Hope this helps.

     

    Best Regards,

    Giotto

    • unnijoy's avatar
      unnijoy
      Post Prodigy
      6 years ago

      v-gizhi-msft  thank you very much.

       

      Can you add this in the powerbi file and share it with me. so it will be very helpfull for me.

       

      Waiting for your reply

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

        Hi,

         

        I am sorry to have kept you waiting, here is my test pbix file:

        pbix 

        If my answer has solved your issue, please mark it as a solution for others to see.
        Thanks!

         

        Best Regards,

        Giotto