Forum Discussion
RANKX Over 3 Fields. ID, DATE, TIME & Direction
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!
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~
2 Replies
- xifeng_L
Super User
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~
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.
