Forum Discussion
Related MAX
Hello all,
I am looking to see the last provider a customer has seen in a calculated column.
Specifically, what provider was last seen by a customer with a completed appointment. I have two tables. A customer table, that has unique customer IDs, and an appointments table which has a listing of all appointments and their assosciated details.
Customer Table
| Customer ID | Last Provider seen? |
| 111 | |
| 111 | |
| 111 | |
| 222 | |
| 222 | |
| 333 |
Appointment Table
| Customer ID | Provider | Appointment Date | Status |
| 111 | J. Doe | 9/5/2020 | Completed |
| 111 | J. Doe | 10/5/2020 | Completed |
| 111 | A. Doe | 11/5/2020 | Completed |
| 222 | B. Doe | 8/5/2020 | Completed |
| 222 | J. Doe | 7/5/2020 | Not Confirmed |
| 333 | C. Doe | 11/5/2020 | Completed |
So far I have been trying to use LOOKUPVALUE, but to no avail. It seems to be pulling data, just not specific to Customer ID.
Here is what the desired output would be in the Customer table:
| Customer ID | Last Provider seen? |
| 111 | A. Doe |
| 222 | B. Doe |
| 333 | C. Doe |
Any help would be greatly appreciated!
Hi jcawley
I build a sample like yours to have a test.
My Customer Table is a calculated table and I think you may build a relationship between Customer ID columns in two tables.
Customer = VALUES(Appointment[Customer ID])Then I add a calculated column in Customer Table.
Last Provider Seen = VAR _LastDate = MAXX ( FILTER ( Appointment, Appointment[Status] = "Completed" && Appointment[Customer ID] = EARLIER ( Customer[Customer ID] ) ), Appointment[Appointment Date] ) RETURN CALCULATE ( MAX ( Appointment[Provider] ), FILTER ( Appointment, Appointment[Customer ID] = EARLIER ( Customer[Customer ID] ) && Appointment[Status] = "Completed" && Appointment[Appointment Date] = _LastDate ) )Result is as below.
You can download the pbix file from this link: Related MAX
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
Hi jcawley
I build a sample like yours to have a test.
My Customer Table is a calculated table and I think you may build a relationship between Customer ID columns in two tables.
Customer = VALUES(Appointment[Customer ID])Then I add a calculated column in Customer Table.
Last Provider Seen = VAR _LastDate = MAXX ( FILTER ( Appointment, Appointment[Status] = "Completed" && Appointment[Customer ID] = EARLIER ( Customer[Customer ID] ) ), Appointment[Appointment Date] ) RETURN CALCULATE ( MAX ( Appointment[Provider] ), FILTER ( Appointment, Appointment[Customer ID] = EARLIER ( Customer[Customer ID] ) && Appointment[Status] = "Completed" && Appointment[Appointment Date] = _LastDate ) )Result is as below.
You can download the pbix file from this link: Related MAX
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you, RicoZhou! That is the ticket!
Much appreciated.
Hi,
Did you make a link between your table ?
https://docs.microsoft.com/en-US/power-bi/transform-model/desktop-create-and-manage-relationships
Have you been trying a RELATED()?
Hello AilleryO,
I did, I'm just not sure how to plug RELATED into what I am looking for. I've only used it for simple things so far.
Try a new column in customer table like
maxx(filter(Appointments, Appointments[Status]="Completed" && Appointments[Customer ID] = Customer[Customer ID]), lastnonblankvalue(Appointments[Appt Date Time], Appointments[Provider]))
Hmm, not quite there! It's certainly doing something, but its not always pulling the max date provider.
