Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi guys,
I'm racking my brains over a problem and I'm not getting anywhere. Even with the search I can't find anything that helps me.
I have a table with bookings of participants for an event, where each row represents a booking by a user (id) with the information when he booked, which event and whether he participated or not.
A user can have attended several times. That means his ID can appear several times.
e.g. given
user_id | event | date | participated |
1 | A | 01.01.2022 | t |
2 | A | 02.01.2022 | f |
3 | B | 02.01.2022 | t |
1 | X | 05.01.2022 | t |
4 | X | 06.01.2022 | t |
3 | Y | 08.01.2022 | f |
1 | Z | 10.01.2022 | t |
... |
In order to analyse how the user behaves with the n-th booking, I tried to add a calculated column in which it should be stated how many times the user booked this booking (row). (whether it was the first booking, second, third etc.).
I had tried to number a subset (of the respective ID) via RANKX, but I couldn't manage to get this number of the rank into the output table respectively I am not yet good enough with DAX to create such a nested function.
What i try to achieve
user_id | event | date | participated | n-th occurence |
1 | A | 01.01.2022 | t | 1 |
2 | A | 02.01.2022 | f | 1 |
3 | B | 02.01.2022 | t | 1 |
1 | X | 05.01.2022 | t | 2 |
4 | X | 06.01.2022 | t | 1 |
3 | Y | 08.01.2022 | f | 2 |
1 | Z | 10.01.2022 | t | 3 |
... |
Can anyone think of something and kindly help me?
Greetings!
Solved! Go to Solution.
Using the RANKX function should get you what you want:
N-th Occurrence =
VAR _user_id = Participants[user_id]
VAR _Result = RANKX( FILTER('Participants',
Participants[user_id] = _user_id ),
Participants[date], ,ASC )
RETURN
_Result
user_ideventdateparticipatedN-th Occurrence
1 | A | Saturday, January 1, 2022 | t | 1 |
2 | A | Tuesday, February 1, 2022 | f | 1 |
3 | B | Tuesday, February 1, 2022 | t | 1 |
1 | X | Sunday, May 1, 2022 | t | 2 |
4 | X | Wednesday, June 1, 2022 | t | 1 |
3 | Y | Monday, August 1, 2022 | f | 2 |
1 | Z | Saturday, October 1, 2022 | t | 3 |
This link explains it well:
https://www.sqlbi.com/articles/introducing-rankx-in-dax/
Regards,
Using the RANKX function should get you what you want:
N-th Occurrence =
VAR _user_id = Participants[user_id]
VAR _Result = RANKX( FILTER('Participants',
Participants[user_id] = _user_id ),
Participants[date], ,ASC )
RETURN
_Result
user_ideventdateparticipatedN-th Occurrence
1 | A | Saturday, January 1, 2022 | t | 1 |
2 | A | Tuesday, February 1, 2022 | f | 1 |
3 | B | Tuesday, February 1, 2022 | t | 1 |
1 | X | Sunday, May 1, 2022 | t | 2 |
4 | X | Wednesday, June 1, 2022 | t | 1 |
3 | Y | Monday, August 1, 2022 | f | 2 |
1 | Z | Saturday, October 1, 2022 | t | 3 |
This link explains it well:
https://www.sqlbi.com/articles/introducing-rankx-in-dax/
Regards,
Thank you very much! 🙂
Your solution is exactly what I had in mind, but I switched the position of RANKX and FILTER (For whatever reason).
Sounds like you just want to COUNTROWS of how many times they particpated?
Would be COUNTROWS by user ID where 'ParticpatedN-TH' = "T" as your filter.
Unfortunately not.
I would like to determine per line how many bookings the user made. In other words, whether it was the first booking, the second booking, the tenth booking, etc.
I am not interested in the total number of bookings of the user.
Thanks for the help anyway! 🙂
Hi,
can you post some more complete data because in the e.g. given there are not sufficient data for calculating the result
I don't have much more than this information. 😅
Theoretically, it should work via the combination of user_id and date.day.
//Edit: As a picture, an exemplary excerpt of the data I have. Due to the high number of bookings, it was not possible for me to quickly create a screenshot where a user_id is present twice. 😅
Hello,
Yes, I just saw that I should have posted the thread in the DAX section.
I tried to implement it with DAX.
User | Count |
---|---|
92 | |
86 | |
66 | |
62 | |
58 |
User | Count |
---|---|
150 | |
113 | |
95 | |
80 | |
72 |