topic Re: Create a Rank column by using a date column. There might be duplicate dates, but the rank should not in DAX Commands and Tips

Create a Rank column by using a date column. There might be duplicate dates, but the rank should not

SriKandimalla — Tue, 31 Mar 2020 22:20:25 GMT

Hello PBI Comunity,

I am trying to solve the following criteria:

[Latest name] is my target DAX column, which I tried to achieve.
I need to check if a name is duplicate, then check all the dates for that name, and give rank for each row even when the dates are repeated.

I appreciate all the help.
TIA

Re: Create a Rank column by using a date column. There might be duplicate dates, but the rank should not

Pragati11 — Wed, 01 Apr 2020 14:22:39 GMT

Hi SriKandimalla.,

Do you have anyother column in your dataset apart from NAME and DATE?

Thanks,

Pragati

Re: Create a Rank column by using a date column. There might be duplicate dates, but the rank should not

SriKandimalla — Wed, 01 Apr 2020 14:26:43 GMT

Yes Pragati11 ,

I have many columns, but I cannot use them. Not relevant to this requirement.

Re: Create a Rank column by using a date column. There might be duplicate dates, but the rank should not

Pragati11 — Wed, 01 Apr 2020 15:18:40 GMT

Hi SriKandimalla ,

This was a tricky thing to solve but finally got a way to do this by creating an INDEX/ID column to the data.

1. Sample data used is as follows:

2. Created the DAX expression column as follows:

Rank = RANKX(FILTER('Name Rank', 'Name Rank'[Name] = EARLIER('Name Rank'[Name])), 'Name Rank'[Date] - 'Name Rank'[ID] / POWER(10, 9), , ASC, Dense)

3. Then I move it to dashboard as follows:

4. Let's check result by filtering down one person:

We finally get the required result 🙂

If this helps and resolves your issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati

Re: Create a Rank column by using a date column. There might be duplicate dates, but the rank should not

Pragati11 — Fri, 03 Apr 2020 10:07:31 GMT

Hi SriKandimalla ,

Just wanted to check, if my latest solutions worked for you? It works in my case. If it works please accept it as a solution ans close the thread.

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

Thanks,

Pragati