Measure for MAX Rank

[Anonymous]

Need help in creating a measure to count the number of promotors based on latest date for NPS .

I have created  Rank using

RANK = RANKX(
FILTER(Table1,
Table1[Topic] = EARLIER(Table1[Topic]) &&
Table1[EmpID]=EARLIER(Table1[EmpID])
),
Table1[Submit_Date].[Date],
,ASC,Dense)

Now I am trying to calculate number of promotors based on MAX Rank. But its not working as expected ..

Measure = CALCULATE(COUNT(Table1[EmpID]),
FILTER(Table1,Table1[type] = "Promoter"),
FILTER(Table1,Table1[Response] = "Yes"),
FILTER(Table1,Table1[Rank] = MAX(Table1[Rank] )

It gives me count 1 for the latest date when I hardcode it with  4 for a particular empid who has 4 rows of data. However when I replace it with Table1[Rank] = MAX(Table1[Rank] it messes up. 

Thanks

[Anonymous]

Hi @Anonymous ,

Replace order by DESC instead of ASC and then hardcode Rank=1 in your measure.

RANK = RANKX(
FILTER(Table1,
Table1[Topic] = EARLIER(Table1[Topic]) &&
Table1[EmpID]=EARLIER(Table1[EmpID])
),
Table1[Submit_Date].[Date],
,DESC,Dense)

Measure = CALCULATE(COUNT(Table1[EmpID]),
FILTER(Table1,Table1[type] = "Promoter"),
FILTER(Table1,Table1[Response] = "Yes"),
FILTER(Table1,Table1[Rank] = 1 )

Best Regards,
Mail2inba4

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

The problem with hardcoding is that if someone changes the date slicer it doesn't work dynamically and is stuck to the latest latest .

So if November is Rank1 and someone changes the date slicer to Oct  - The rank doesn't change