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, w...
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 ,
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,
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).
Anonymous ,
My pleasure. Glad to hear it!