Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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...
Need Help
  • rsbin's avatar
    rsbin
    3 years ago

    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
   _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,

serpiva64's avatar
serpiva64
Solution Sage
3 years ago

Hi,

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

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