Forum Discussion

jeroenwmwillems's avatar
jeroenwmwillems
Icon for Advocate I rankAdvocate I
3 years ago
Solved

Help in first occurance based on three columns

Hi all,

 

Hope you can share your wisdom with me and help me set up a column query for the following:

I'm trying to create a colum that tags the first visit of a user to a location. I have a date column, a location column and a user column.

What formula would I have to write up to get the output as in my screenshot?

 

Thanks in advance for your help on this one! 

 

 

Need Help
Tips and Tricks
  • rajulshah's avatar
    rajulshah
    3 years ago

    Hello jeroenwmwillems ,

     

    You can use the following DAX query for a calculated column:

     

    Output = 
VAR FirstVisitDate =
    CALCULATE (
        MIN ( Visits[Date] ),
        FILTER (
            Visits,
            Visits[User] = EARLIER ( Visits[User] )
            && Visits[Location] = EARLIER ( Visits[Location] )
                && Visits[Date] <= EARLIER ( Visits[Date] )
        )
    )
VAR FirstVisitLocation =
    CALCULATE (
        MIN ( Visits[Location] ),
        FILTER (
            Visits,
            Visits[User] = EARLIER ( Visits[User] )
            && Visits[Location] = EARLIER ( Visits[Location] )
                && Visits[Date] >= EARLIER ( Visits[Date] )
        )
    )
VAR SelectedUser = Visits[User]
RETURN
    IF (
        Visits[User] = SelectedUser
            && Visits[Date] = FirstVisitDate
            && Visits[Location] = FirstVisitLocation,
        "First Visit",
        "Follow up Visit"
    )

     

     

    Please try this and let me know if this didn't work.

2 Replies

  • rajulshah's avatar
    rajulshah
    Icon for Resident Rockstar rankResident Rockstar
    3 years ago

    Hello jeroenwmwillems ,

     

    You can use the following DAX query for a calculated column:

     

    Output = 
VAR FirstVisitDate =
    CALCULATE (
        MIN ( Visits[Date] ),
        FILTER (
            Visits,
            Visits[User] = EARLIER ( Visits[User] )
            && Visits[Location] = EARLIER ( Visits[Location] )
                && Visits[Date] <= EARLIER ( Visits[Date] )
        )
    )
VAR FirstVisitLocation =
    CALCULATE (
        MIN ( Visits[Location] ),
        FILTER (
            Visits,
            Visits[User] = EARLIER ( Visits[User] )
            && Visits[Location] = EARLIER ( Visits[Location] )
                && Visits[Date] >= EARLIER ( Visits[Date] )
        )
    )
VAR SelectedUser = Visits[User]
RETURN
    IF (
        Visits[User] = SelectedUser
            && Visits[Date] = FirstVisitDate
            && Visits[Location] = FirstVisitLocation,
        "First Visit",
        "Follow up Visit"
    )

     

     

    Please try this and let me know if this didn't work.