Most Recently Seen Provider

[mattramirez2020]

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!

[amitchandak]

@mattramirez2020 , Create a new column in Customer table as

maxx(filter(Appointment, Appointment[CustomerID] = Customer[CustomerID]),[ApptDate])

[mattramirez2020]

@amitchandak So I have that formula already for Date of Last Visit! I actually need the corresponding Provider for that date!

[v-jayw-msft · Accepted solution]

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