Forum Discussion
jeroenwmwillems
Advocate I
3 years agoHelp 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 ...
- 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.
rajulshah
Resident Rockstar
3 years agoHello 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
Advocate I
3 years agoThanks heaps for this, that worked!