cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TabathaN
Advocate I
Advocate I

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 IDAppointment DateAppointment StatusScheduling ProviderAppointment Type
3287751/1/2020f - FilledDr ANew Patient
4191041/1/2020f - FilledDr ANew Patient
 1/1/2020o - Open SlotDr ANew Patient
1629811/1/2020f - FilledDr ANew Patient
4321231/2/2020f - FilledDr AFollow Up
4321231/2/2020x - CancelledDr AFollow Up
 1/2/2020o - Open SlotDr AFollow Up
2095211/2/2020f - FilledDr AFollow Up
2882031/3/2020f - FilledDr AStudy
 1/3/2020o - Open SlotDr AStudy
3346411/3/2020f - FilledDr AStudy
2984211/3/2020f - FilledDr AStudy
 1/10/2020o - Open SlotDr AFollow Up
 1/15/2020o - Open SlotDr AStudy
 1/20/2020o - Open SlotDr ANew Patient
 3/2/2020o - Open SlotDr ANew Patient
 2/1/2020o - Open SlotDr BNew Patient
4541212/1/2020f - FilledDr BFollow Up
 2/2/2020o - Open SlotDr BStudy
2459912/2/2020f - FilledDr BNew Patient
3338132/3/2020f - FilledDr BFollow Up
1868582/3/2020f - FilledDr BStudy
 2/10/2020o - Open SlotDr BStudy
 2/15/2020o - Open SlotDr BNew Patient
 2/20/2020o - Open SlotDr BFollow Up
 2/25/2020o - Open SlotDr BStudy
 2/28/2020o - Open SlotDr BNew Patient
 4/5/2020o - Open SlotDr BNew Patient
 4/30/2020o - Open SlotDr BFollow Up
 2/3/2021o - Open SlotDr BFollow Up
 3/1/2020o - Open SlotDr CNew Patient
3393393/1/2020f - FilledDr CFollow Up
 3/2/2020o - Open SlotDr CStudy
3945453/2/2020f - FilledDr CNew Patient
 3/3/2020o - Open SlotDr CFollow Up
2562153/3/2020f - FilledDr CStudy
2506823/3/2020f - FilledDr CNew Patient
 3/10/2020o - Open SlotDr CFollow Up
 3/15/2020o - Open SlotDr CStudy
 3/20/2020o - Open SlotDr CNew Patient
 3/25/2020o - Open SlotDr CFollow Up
 5/1/2020o - Open SlotDr CStudy
 5/15/2020o - Open SlotDr CNew Patient
 5/31/2020o - Open SlotDr CFollow Up
1 ACCEPTED 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
    )
)

Capture4.JPG

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.

View solution in original post

5 REPLIES 5
kentyler
Solution Sage
Solution Sage

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

appointment_status_star.png

Then I put the appoint table on a report and created a slicer for doctor and a slicer for status

appointment_status.png

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.





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


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.

Provider3rd Next Avail
Dr A1/3/2020
Dr B2/10/2020
Dr C3/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?

 

Provider3rd Next New Patient
Dr A3/2/2020
Dr B2/28/2020
Dr C5/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
    )
)

Capture4.JPG

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.

View solution in original post

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 ?





Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!




Join the conversation at We Talk BI find out more about me at Slow BI


@kentyler correct, no need to know what the other openings are - just need the 3rd.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.