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.

kentyler's avatar
kentyler
Solution Sage
6 years ago

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.

  • 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.

    • v-juanli-msft's avatar
      v-juanli-msft
      Community Support
      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.