Forum Discussion
Calculate Column: How many occurences
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!
Anonymous ,
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 _Resultuser_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,
9 Replies
Anonymous ,
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 _Resultuser_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,
Anonymous ,
My pleasure. Glad to hear it!
Anonymous
Hi,
Do you want to do this in DAX or Power query?
Thank you
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. 😅
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.
