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 ...
  • rajulshah's avatar
    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.