Forum Discussion

TabathaN's avatar
TabathaN
Advocate III
6 years ago
Solved

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...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    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.

TabathaN's avatar
TabathaN
Advocate III
6 years ago

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
kentyler's avatar
kentyler
Solution Sage
6 years ago

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 ?

  • TabathaN's avatar
    TabathaN
    Advocate III
    6 years ago

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