Forum Discussion
Most Recently Seen Provider
Hello!
I am trying to create a calculated column in a customer table that gives me the most recently seen provider from an appointment table. I have been able to make a column that gives me their most recently completed appointment date but for some reason, I can't make giving the corresponding provider work.
Any help would be greatly appreciated!
Table: Appointment
| CustomerID | ApptDate | Provider | Status |
| 1 | 1/1/2020 | AB | Completed |
| 1 | 6/5/2020 | BC | Completed |
| 1 | 12/12/2020 | DF | Not-Confirmed |
| 2 | 4/8/2020 | AB | Completed |
| 3 | 6/12/2020 | DE | No-Show |
| 3 | 11/15/2020 | GH | Not-Confirmed |
Table: Customer
| CustomerID | Date of Last Completed Visit | Last Visit Provider (what I need!) |
| 1 | 6/5/2020 | BC |
| 2 | 4/8/2020 | AB |
| 3 |
What I am looking for:
A calculated column for Last Visit Provider that looks at the appointment table and for an appointment with a status of "Completed".
Ideally, I think a calculated column would be the best thing but if anyone thinks I should make a measure instead, let me know!
Hi mattramirez2020 ,
Check the formulas
Column = CALCULATE(MAX(Appointment[ApptDate]),FILTER(Appointment,Appointment[CustomerID]=Customer[CustomerID]&&Appointment[Status]="completed")) Column 2 = CALCULATE(MAX(Appointment[Provider]),FILTER(Appointment,Appointment[CustomerID]=Customer[CustomerID]&&Appointment[ApptDate]=Customer[Column]))Result would be shown as below.
Best Regards,
Jay
3 Replies
mattramirez2020 , Create a new column in Customer table as
maxx(filter(Appointment, Appointment[CustomerID] = Customer[CustomerID]),[ApptDate])
amitchandak So I have that formula already for Date of Last Visit! I actually need the corresponding Provider for that date!
Hi mattramirez2020 ,
Check the formulas
Column = CALCULATE(MAX(Appointment[ApptDate]),FILTER(Appointment,Appointment[CustomerID]=Customer[CustomerID]&&Appointment[Status]="completed")) Column 2 = CALCULATE(MAX(Appointment[Provider]),FILTER(Appointment,Appointment[CustomerID]=Customer[CustomerID]&&Appointment[ApptDate]=Customer[Column]))Result would be shown as below.
Best Regards,
Jay
