RANKX for multiple columns including date column with slicer on date

[nidhs909]

Hello All, 

I have a EmpID and Date Data as above. what I am trying to achieve is the rank considering both the columns. It should be as a measure as i need a date as slicer. 

Thanks in advance. 

[Anonymous]

Hi, @rajendraongole1 

Based on your information, I create a table:

Then create a new measure and use the following dax:

Rank = ROWNUMBER(ALLSELECTED('Table'[EmpID],'Table'[Custom Date]),ORDERBY('Table'[EmpID],ASC,'Table'[Custom Date],DESC))

Here is my preview:

Even if the date is changed, its ranking changes dynamically:

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

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

[nidhs909]

This is what i would like to achieve. 

EmpID	Date	Rank
5333	1st Jan, 2024	1
5333	2nd Jan, 2024	2
5333	2nd Jan, 2024	2
5333	3rd Jan, 2024	3
5075	1st Jan, 2024	1
5075	2nd Jan, 2024	2
5075	3rd Jan, 2024	3
5075	3rd Jan, 2024	3

[Anonymous]

Hi, @nidhs909 

You can use the following DAX expression:

RankMeasure = 
VAR _currentID = MAX('Table'[EmpID])
RETURN
CALCULATE(
    RANKX(
        FILTER(ALLSELECTED('Table'),'Table'[EmpID]=_currentID),
        CALCULATE(MAX('Table'[Custom Date])),
        ,
        ASC,
        Dense
    ),
    ALLEXCEPT('Table', 'Table'[Custom Date])
)

Here is my preview:

However, the Table visual will automatically aggregate the same value, for example, the same date and the same ID will be aggregated into the same row of data, so I created an Index in PowerQuery in advance to show the order when duplicated.

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

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

[nidhs909 · Accepted solution]

Thanks alot, worked like a charm!

[rajendraongole1]

Hi @nidhs909 -If you don't already have a date table, you can create one as below:

DateTable = CALENDAR(MIN(EmpData[Date]), MAX(EmpData[Date]))

establish a relationship between date table and employee table and use the below measure.

Please create the below measure

EmpRank =
VAR SelectedDate = SELECTEDVALUE(DateTable[Date])
RETURN
RANKX(
FILTER(
EmpData,
EmpData[Date] <= SelectedDate
),
EmpData[EmpID],
,
ASC,
Dense
)

Hope it works

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[nidhs909]

Unfortunately, it gives this error. 

A single value for column 'EmpID' in table 'XXX' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.