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 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 |
Solved! Go to Solution.
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.
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 table
Then 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.
Help when you know. Ask when you don't!
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 |
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.
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 ?
Help when you know. Ask when you don't!
@kentyler correct, no need to know what the other openings are - just need the 3rd.
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |