Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Customer ID | Last Provider seen? |
111 | A. Doe |
222 | B. Doe |
333 | C. Doe |
Solved! Go to Solution.
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.
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.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
83 | |
67 | |
59 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |