Help in first occurance based on three columns

[jeroenwmwillems]

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! 

[rajulshah · Accepted solution]

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.

[jeroenwmwillems]

Thanks heaps for this, that worked!