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!
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!
Solved! Go to 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
@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
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 |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |