Forum Discussion
Calculate 3rd Opening
Hello,
I work for a medical practice and we are looking to create a table/matrix that shows the 3rd next opening on a providers schedule. I am at a loss on how to accomplish this and could really use some help.
Of the data below, I would expect a returned result of:
Dr A 3rd next opening = 01/03/2020
Dr A 3rd next new patient opening = 03/02/2020
| Patient ID | Appointment Date | Appointment Status | Scheduling Provider | Appointment Type |
| 328775 | 1/1/2020 | f - Filled | Dr A | New Patient |
| 419104 | 1/1/2020 | f - Filled | Dr A | New Patient |
| 1/1/2020 | o - Open Slot | Dr A | New Patient | |
| 162981 | 1/1/2020 | f - Filled | Dr A | New Patient |
| 432123 | 1/2/2020 | f - Filled | Dr A | Follow Up |
| 432123 | 1/2/2020 | x - Cancelled | Dr A | Follow Up |
| 1/2/2020 | o - Open Slot | Dr A | Follow Up | |
| 209521 | 1/2/2020 | f - Filled | Dr A | Follow Up |
| 288203 | 1/3/2020 | f - Filled | Dr A | Study |
| 1/3/2020 | o - Open Slot | Dr A | Study | |
| 334641 | 1/3/2020 | f - Filled | Dr A | Study |
| 298421 | 1/3/2020 | f - Filled | Dr A | Study |
| 1/10/2020 | o - Open Slot | Dr A | Follow Up | |
| 1/15/2020 | o - Open Slot | Dr A | Study | |
| 1/20/2020 | o - Open Slot | Dr A | New Patient | |
| 3/2/2020 | o - Open Slot | Dr A | New Patient | |
| 2/1/2020 | o - Open Slot | Dr B | New Patient | |
| 454121 | 2/1/2020 | f - Filled | Dr B | Follow Up |
| 2/2/2020 | o - Open Slot | Dr B | Study | |
| 245991 | 2/2/2020 | f - Filled | Dr B | New Patient |
| 333813 | 2/3/2020 | f - Filled | Dr B | Follow Up |
| 186858 | 2/3/2020 | f - Filled | Dr B | Study |
| 2/10/2020 | o - Open Slot | Dr B | Study | |
| 2/15/2020 | o - Open Slot | Dr B | New Patient | |
| 2/20/2020 | o - Open Slot | Dr B | Follow Up | |
| 2/25/2020 | o - Open Slot | Dr B | Study | |
| 2/28/2020 | o - Open Slot | Dr B | New Patient | |
| 4/5/2020 | o - Open Slot | Dr B | New Patient | |
| 4/30/2020 | o - Open Slot | Dr B | Follow Up | |
| 2/3/2021 | o - Open Slot | Dr B | Follow Up | |
| 3/1/2020 | o - Open Slot | Dr C | New Patient | |
| 339339 | 3/1/2020 | f - Filled | Dr C | Follow Up |
| 3/2/2020 | o - Open Slot | Dr C | Study | |
| 394545 | 3/2/2020 | f - Filled | Dr C | New Patient |
| 3/3/2020 | o - Open Slot | Dr C | Follow Up | |
| 256215 | 3/3/2020 | f - Filled | Dr C | Study |
| 250682 | 3/3/2020 | f - Filled | Dr C | New Patient |
| 3/10/2020 | o - Open Slot | Dr C | Follow Up | |
| 3/15/2020 | o - Open Slot | Dr C | Study | |
| 3/20/2020 | o - Open Slot | Dr C | New Patient | |
| 3/25/2020 | o - Open Slot | Dr C | Follow Up | |
| 5/1/2020 | o - Open Slot | Dr C | Study | |
| 5/15/2020 | o - Open Slot | Dr C | New Patient | |
| 5/31/2020 | o - Open Slot | Dr C | Follow Up |
Hi TabathaN
Create three measures
Measure = MAX('Table'[Appointment Date]) 3rd Next Avail = CALCULATE ( MAX ( 'Table'[Appointment Date] ), TOPN ( 3, FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Scheduling Provider] = MAX ( 'Table'[Scheduling Provider] ) && FIND ( "Open", 'Table'[Appointment Status], 1, 0 ) > 0 ), [Measure], ASC ) ) 3rd Next New Patient = CALCULATE ( MAX ( 'Table'[Appointment Date] ), TOPN ( 3, FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Scheduling Provider] = MAX ( 'Table'[Scheduling Provider] ) && FIND ( "Open", 'Table'[Appointment Status], 1, 0 ) > 0 && 'Table'[Appointment Type] = "New Patient" ), [Measure], ASC ) )Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
Hello,
While I was working on your question Power BI produced one of its marvelous surpsizes
I added a Doctor dimension and a Status dimension tableThen I put the appoint table on a report and created a slicer for doctor and a slicer for status
Without adding any DAX you can now filter by doctor and status, or just by doctor, or just by status. This version is more powerful than what you asked for.... but I thought it might be very helpful
If you'd still like a dax measure to get the next 2 open appointments let me know and I can write that for you.I'm a personal Power BI trainer. Every time I answer a question I learn something new.
thank you kentyler ! I definitely will use the slicer functionality but here's what I'm thinking...
I would like the visual to list the singular record of the 3rd next available date rather than users having to look and figure it out.
Provider 3rd Next Avail Dr A 1/3/2020 Dr B 2/10/2020 Dr C 3/3/2020 And then whether we would need to create a second visual, or add slicer to say "ok, now what is the 3rd next New Patient, not just overall 3rd next". So kinda looking for two things but priority is the overall 3rd next avail. Does that make sense?
Provider 3rd Next New Patient Dr A 3/2/2020 Dr B 2/28/2020 Dr C 5/15/2020 OK
So if the user selects a Doctor who as 6 future openings, you want a single card that displays the date of the 3rd of those openings, but not the 1st or 2nd ?
