Calculate Column: How many occurences

[Chuck1892]

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!

[rsbin · Accepted solution]

@Chuck1892 ,

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,

[Chuck1892]

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).

[rsbin]

@Chuck1892 ,

My pleasure.  Glad to hear it!

[lg1551]

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.

[Chuck1892]

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! 🙂

[serpiva64]

Hi,

can you post some more complete data because in the e.g. given there are not sufficient data for calculating the result 

[Chuck1892]

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.  😅

[Dinesh_Suranga]

@Chuck1892 

Hi,

Do you want to do this in DAX or Power query?

Thank you

[Chuck1892]

Hello,

Yes, I just saw that I should have posted the thread in the DAX section.

I tried to implement it with DAX.