Need Help on Dax

[Anonymous]

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	LH	MM
MH	HH	MH

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

[tamerj1]

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]

[Tanushree_Kapse · Accepted solution]

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