RANKX Over 3 Fields. ID, DATE, TIME & Direction

[SNRCON81]

Hi All,

First post, hoping you can help me 🙂 

I'm writing some DAX code (Using the DAX query window to test)  to create a table that will give me a rank over my ID, Date, Time and direction fields

Sample Data...

Sheet1[Id]	[Date]	[Time]	Sheet1[Direction]	Sheet1[RANK]	[NEW RANK]
1	01/05/2024	07:45:00	In	1	1
1	01/05/2024	09:45:00	In	2	2
1	01/05/2024	10:46:00	Out	1	1
1	01/05/2024	11:02:00	Out	2	2
1	01/05/2024	12:02:00	Out	3	3
1	01/05/2024	13:00:00	In	1	3
1	01/05/2024	13:00:00	Out	1	4
1	01/05/2024	13:02:00	In	1	4
1	01/05/2024	15:05:00	Out	1	5
1	01/05/2024	18:33:00	In	1	5
2	01/05/2024	10:25:00	Out	1	1
2	01/05/2024	13:25:00	Out	2	2
2	01/05/2024	15:33:00	In	1	1
2	01/05/2024	16:00:00	In	2	2
2	01/05/2024	16:55:00	In	3	3
2	01/05/2024	17:21:00	Out	1	3
2	01/05/2024	18:00:00	Out	2	4
2	01/05/2024	19:33:00	Out	3	5
2	01/05/2024	19:34:00	In	1	4
2	01/05/2024	20:00:00	In	2	5

Below is my code

EVALUATE

SELECTCOLUMNS(
	Sheet1,
	Sheet1[Id],
	"Date",FORMAT(Sheet1[Date],"dd/mm/yyyy"),
	"Time",FORMAT(Sheet1[Time],"hh:mm:ss"),
	Sheet1[Direction],
	Sheet1[RANK],
	"NEW RANK",
	
RANKX(
    FILTER(
        Sheet1,
        Sheet1[Id] = EARLIER(Sheet1[Id]) &&
        Sheet1[Date] = EARLIER(Sheet1[Date]) &&
        Sheet1[Direction] = EARLIER(Sheet1[Direction])
    ),
    Sheet1[Time],
    ,
    ASC,
    DENSE
))

I'm getting the "NEW RANK" field and I want the 'Sheet1'[Rank] field (I just wrote this out in Excel)

The first 2 groups of "In" and "Out" work well but row 6 goes to 3 and does not reset back to 1 as desired

Feels like I’m close , any help would be greatly appreciated 🙂

Many thanks!

[xifeng_L · Accepted solution]

Hi @SNRCON81 ,

You can try below dax query.

EVALUATE
VAR vTb1 = 
	SELECTCOLUMNS(
		Sheet1,
		Sheet1[Id],
		"Date",FORMAT(Sheet1[Date],"dd/mm/yyyy"),
		"Time",FORMAT(Sheet1[Time],"hh:mm:ss"),
		"Index",ROUND(Sheet1[Date]+Sheet1[Time],4)&'Sheet1'[direction],
		Sheet1[Direction],
		Sheet1[RANK]
	)
VAR vTb2 = 
	ADDCOLUMNS(
		vTb1,
		"Grp",
			SUMX(
				FILTER(vTb1,[Index]<=EARLIER([Index]) && 'Sheet1'[id]=EARLIER('Sheet1'[id])),
				VAR CurId = 'Sheet1'[id]
				VAR CurIndex = [Index]
				VAR CurDirection = 'Sheet1'[direction]
				VAR PreDirection = 
					MAXX(
						TOPN(1,FILTER(vTb1,'Sheet1'[id]=CurId && [Index]<CurIndex),[Index]),
						'Sheet1'[direction]
					)
				RETURN
				IF(CurDirection<>PreDirection,1,0)
			)
	)
RETURN
ADDCOLUMNS(
	vTb2,
	"NEW RANK",
		RANKX(
			FILTER(vTb2,'Sheet1'[id]=EARLIER('Sheet1'[id]) && [Grp]=EARLIER([Grp])),
			[Time],,ASC,Dense
		)
)
	

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

[SNRCON81]

Excellent!

I've Just tested my proof of concept locally and it worked like a charm

I need to re-produce on the client side and test on a wider dataset, but the solution here works as intended with the above info provided 🙂
Thanks again.