The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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!
Solved! Go to 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~
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
62 | |
32 | |
21 | |
16 | |
15 |
User | Count |
---|---|
117 | |
34 | |
30 | |
24 | |
21 |