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 Marker 2019 Year-End Marker 2020 Year-End Marker HM MM MH MM HM MM HM MH HM HM MM MM ML LL LM MM L...
  • 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
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity 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]