Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Need Help on Dax

I have table  with below columns with like this

2018 Year-End Marker2019 Year-End Marker2020 Year-End Marker
HMMMMH
MMHMMM
HMMHHM
HMMMMM
MLLLLM
MMLHMM
MHHHMH


And I have date slicer with Years 2021, 2020 and 2019. I need to calculate If I select year 2021, I need to get the count of c(HH,HM,MH) in 2020 Yearend Marker and If I select year 2020, I need to get the count of c(HH,HM,MH) in 2019 Year end Marker.

I used this following expression and its not working.

Promotions =
IF(SELECTEDVALUE(Dim_Date, Dim_Date[Year] ="2021"), Calculate(COUNT(EmployeeMaster[2020 Year-End Marker]),
FILTER(EmployeeMaster, [2020 Year-End Marker] in {"HH","HM","MH"})),
IF(SELECTEDVALUE(Dim_Date[Year] ="2020"), Calculate(COUNT(EmployeeMaster[2019 Year-End Marker]),
FILTER(EmployeeMaster, [2020 Year-End Marker] in {"HH","HM","MH"})), "_"))

Please help
  • Tanushree_Kapse's avatar
    Tanushree_Kapse
    4 years ago

    Hi Anonymous 

     

    Use the below measure:

    if(SELECTEDVALUE(Dim_Date[Year])="2021",CALCULATE(COUNT('Sample'[2020 Year-End Marker]), 'Sample'[2020 Year-End Marker] in {"HH","HM","MH"}),if(SELECTEDVALUE(Dim_Date[Year])="2020",CALCULATE(COUNT('Sample'[2020 Year-End Marker]), 'Sample'[2019 Year-End Marker] in {"HH","HM","MH"}),"_"))
     
    and make sure that the Year Column has Text datatype.
     
     
    Mark this as a solution, if I answered your question. Kudos are always appreciated.
    Thanks

2 Replies

  • Tanushree_Kapse's avatar
    Tanushree_Kapse
    Impactful Individual
    4 years ago

    Hi Anonymous 

     

    Use the below measure:

    if(SELECTEDVALUE(Dim_Date[Year])="2021",CALCULATE(COUNT('Sample'[2020 Year-End Marker]), 'Sample'[2020 Year-End Marker] in {"HH","HM","MH"}),if(SELECTEDVALUE(Dim_Date[Year])="2020",CALCULATE(COUNT('Sample'[2020 Year-End Marker]), 'Sample'[2019 Year-End Marker] in {"HH","HM","MH"}),"_"))
     
    and make sure that the Year Column has Text datatype.
     
     
    Mark this as a solution, if I answered your question. Kudos are always appreciated.
    Thanks
  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi Anonymous 
    Hear is the sample file with the solution https://www.dropbox.com/t/Iw44tUGijEnBmA1z
    The best approach is to have your data arranged in a proper way. You need to unpivot your data into only two columns (one for the year and one for the year end marker). This is simple just follow below steps

    Once the data is ready the rest is simple. First set your relationship as follows:

    Then write your measure 

    Promotions = 
CALCULATE (
    COUNTROWS ( 
        FILTER ( 
            EmployeeMaster, 
            EmployeeMaster[Year-End Marker] IN { "HH", "HM", "MH" } 
        )
    ),
    Dim_Date[Year] = MAX ( Dim_Date[Year] ) - 1
)

    finally build your report where you can slice by Dim_Date[Year]