cancel
Showing results for
Did you mean:

## 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

1 ACCEPTED SOLUTION
Community Support

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 5
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

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.

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.

 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
Community Support

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.

Solution Sage

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.

Announcements

#### Launching new user group features

Learn how to create your own user groups today!