index/rank/generate for groups with dates

[ncraft]

Hi team, 

I am stumbling in the dark on this one. I feel like I should be able to solve this but I just cant get there for some reason. 
I have a table of data that I need to create an index/rank for. The table has a column for names/IDs and dates: 

date	name
1/31/2022	A
2/1/2022	A
2/2/2022	A
2/3/2022	A
2/4/2022	A
2/7/2022	A
2/8/2022	A
2/9/2022	A
2/10/2022	A
2/11/2022	A
2/1/2022	B
2/2/2022	B
2/3/2022	B
2/4/2022	B
2/7/2022	B
2/8/2022	B
2/9/2022	B
2/10/2022	B
2/11/2022	B
1/31/2022	C
2/1/2022	C
2/2/2022	C
2/3/2022	C
2/4/2022	C
2/7/2022	C
2/8/2022	C
2/9/2022	C
2/10/2022	C

Note how the date column is not unique but any name & date combination is unique. In this sample data, I have sorted the columns to group the names and rank the dates from min to max, but in the actual data table, they are unsorted. It may not be necessary to point it out but not all [name]s have the same dates. For example, "1/31/2022 & A" exists, "1/31/2022 & C" exists but "1/31/2022 & B" dose NOT exist. 
I need an index that ranks these dates for each name to produce the following result. 

date	name	Index_result
1/31/2022	A	1
2/1/2022	A	2
2/7/2022	B	5
2/8/2022	B	6
2/2/2022	B	2
2/3/2022	B	3
2/1/2022	C	2
2/4/2022	C	5
1/31/2022	C	1
2/10/2022	C	9
2/2/2022	A	3
2/3/2022	A	4
2/7/2022	C	6
2/10/2022	A	9
2/11/2022	A	10
2/1/2022	B	1
2/9/2022	A	8
2/10/2022	B	8
2/11/2022	B	9
2/2/2022	C	3
2/3/2022	C	4
2/4/2022	A	5
2/8/2022	A	7
2/9/2022	B	7
2/7/2022	A	6
2/4/2022	B	4
2/8/2022	C	7
2/9/2022	C	8

If possible, I'd like to know how to do this in both DAX as a calculated column and in Poiwer M Quary as a transform. 

Thank you!

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

[MarkyMark1]

How do you update this to rank where the dates are the same for the same name but you need a different rank to only get the first date with the same name?

[ncraft]

@Jihwan_Kim, This solution works wonderfully as a calculated column. But what about as a transform step in Power Query Editor?

The reason I need it in Power M Query, is becuase I want to be able to perform a transformation on a seperate table that uses a merg queries to bring the index over to a new table of related data. If the index column only exisists as a calculated column, it wont be avalible in power query edidtor for transformations. 

[ncraft]

Oh, I am sorry @Jihwan_Kim , I just noticed you included the PBIX file with the transform solutions on the sample data. 
Let me implement that now and see if it completes the solution. 

[ncraft]

YES! Your code @Jihwan_Kim for power M Query solved this exactly as expected. 
Thank you!

[TheoC]

Hi @ncraft 

You can use the following column to achieve what you're after:

Columm = RANKX ( FILTER ( 'Table' , 'Table'[Name] = EARLIER ( 'Table'[Name]' ) ) , 'Table'[Date] )

Hopefully this helps.

In terms of using Power Query, using Transform unfortunately won't get you the outcome you need. You will need to use INDEX and a few other steps to get there.

Let me know if it's something you really need if a column is insufficient because it can get a little tedious. 

Thanks,

Theo

[CNENFRNL]

A simplest Excel worksheet formula is enough

[ncraft]

thank you but the project I am working on is a large report in Power BI. The requested solution here is just a small piece of a much larger puzzle. A solution in excel is not feezable. 

[TheoC]

Hi @ncraft 

You can use the following column to achieve what you're after:

Columm = RANKX ( FILTER ( 'Table' , 'Table'[Name] = EARLIER ( 'Table'[Name]' ) ) , 'Table'[Date] )

Hopefully this helps.

In terms of using Power Query, using Transform unfortunately won't get you the outcome you need. You will need to use INDEX and a few other steps to get there.

Let me know if it's something you really need if a column is insufficient because it can get a little tedious. 

Thanks,

Theo

[ncraft]

Thanks @TheoC , The calculated column helps me build a simple test to make sure the solution works, but in the end, I still need the solution to be written in Power Query. 

The reason I need it in Power M Query, is because I want to be able to perform a transformation on a separate table that uses a merge queries to bring the index over to a new table of related data. If the index column only exists as a calculated column, it wont be avalible in power query editor for transformations. 

For more detail, I have the first table of data described in my sample - lets call it the 'Calendar' table - but I also have a second table with more dates, names and groups - lets call it 'entries' 
It looks a little something like this:

Name	Group	Date
A	1	1/31/2022
A	1	2/1/2022
B	3	2/1/2022
B	4	2/1/2022
C	5	2/8/2022
C	6	2/4/2022

Note how the name column on this 'entries' table matches the first name column on the 'calendar' table. None of the columns on the 'entries' table contain unique values, but any combination of [Name] & [Group] & [Date] will be unique. Also, any combination of [Group] & [Date] will be unique, but there will be duplicates for combinations of [Name] & [Date] and [Name] & [Group]. 

Once, I have the proper index column on the 'calendar' column, I plan to use merge quarries to then add that index column to the 'entries' table, such that the [date] values have the appropriate ranking by [name] on both tables. I need this to perform further calculations on those indexes by the [Group] values on the 'entries' table. 

[TheoC]

@ncraft makes a lot of sense. I am glad that @Jihwan_Kim could provide the solution and PBIX to help. All the best in your Power BI journey! Theo