Forum Discussion

puru85's avatar
puru85
Helper II
2 years ago
Solved

Calculating Wait Time for Outpatients

Hi Experts,

I need assistance with calculating the wait time for outpatients in Power BI.

My dataset consists of two tables:

Appointment: Contains details about scheduled appointments, including the appointment time.
OutpatientVisit: Captures information on outpatient visits, including timestamps for 'Checked In' and 'Consult Start' statuses.

Requirement:

I need to calculate the wait time for outpatients based on the following logic:

Wait Time =
IF (
OutpatientVisit.VisitTime (VisitStatus = 'Checked In') < Appointment.AppointmentTime,
OutpatientVisit.VisitTime (VisitStatus = 'Consult Start') - Appointment.AppointmentTime,
OutpatientVisit.VisitTime (VisitStatus = 'Consult Start') - OutpatientVisit.VisitTime (VisitStatus = 'Checked In')
)


I achieved this by creating a calculated column in the OutpatientVisit table, named WaitTime_1.
However, I need to update the DAX formula to handle cases where an outpatient ID is not found in the Appointment table. In such cases, I want to calculate the wait time as:

OutpatientVisit.VisitTime (VisitStatus = 'Consult Start') - OutpatientVisit.VisitTime (VisitStatus = 'Checked In')

How can I achieve this in Power BI using DAX? I am open to using either a calculated column.

Attached is the  PBIX file.
Waittime.pbix

Thank you for your help!

General Comment
Need Help
Show and Tell
Tips and Tricks
  • Moetazzahran's avatar
    Moetazzahran
    2 years ago

    Hello puru85 , 
    You can use this measure to accomedate the additional condition you need.

    WaitTime =
    VAR AppointmentTime =
    MAXX (
    RELATEDTABLE(Appointment),
    Appointment[AppointmentTime]
    )

    VAR CheckedInTime =
    CALCULATE (
    MAX (OutpatientVisit[VisitTime]),
    OutpatientVisit[VisitStatus] = "Checked In",ALLEXCEPT(OutpatientVisit,OutpatientVisit[OupatientIDAndDate],OutpatientVisit[VisitDate])
    )

    VAR ConsultStartTime =
    CALCULATE (
    MAX (OutpatientVisit[VisitTime]),
    OutpatientVisit[VisitStatus] = "Consult Start",ALLEXCEPT(OutpatientVisit,OutpatientVisit[OupatientIDAndDate],OutpatientVisit[VisitDate])
    )


    RETURN
    SWITCH(TRUE(),
    ISBLANK(AppointmentTime),
    DATEDIFF(CheckedInTime,ConsultStartTime,MINUTE),
    CheckedInTime < AppointmentTime,
    DATEDIFF(CheckedInTime,ConsultStartTime,MINUTE),
    DATEDIFF(CheckedInTime,ConsultStartTime,MINUTE)
    )

     


     


    The Highlight for this measure that it can I used 
    ALLEXCEPT(OutpatientVisit,OutpatientVisit[OupatientIDAndDate],OutpatientVisit[VisitDate])
    Which applies the correct filter context for the last condition you required. 

    If this solves your issue, please accept it as a solution. 

    You Kudos will be greatly appreciated.
  • Moetazzahran's avatar
    Moetazzahran
    2 years ago

    Hello puru85 , 
    I believe if you need the desired result you can try this out as a calculated column.

    WaitTime_calcc =
    VAR AppointmentTime =
    MAXX (
    RELATEDTABLE(Appointment),
    Appointment[AppointmentTime]
    )

    VAR CheckedInTime =
    CALCULATE (
    MAX (OutpatientVisit[VisitTime]),
    OutpatientVisit[VisitStatus] = "Checked In",ALLEXCEPT(OutpatientVisit,OutpatientVisit[OupatientID],OutpatientVisit[VisitDate])
    )

    VAR ConsultStartTime =
    CALCULATE (
    MAX (OutpatientVisit[VisitTime]),
    OutpatientVisit[VisitStatus] = "Consult Start",ALLEXCEPT(OutpatientVisit,OutpatientVisit[OupatientID],OutpatientVisit[VisitDate])
    )


    RETURN
    SWITCH(TRUE(),
    ISBLANK(RELATED(Appointment[OupatientIDAndDate]))&&OutpatientVisit[VisitStatus]="Consult Start",
    DATEDIFF(CheckedInTime,ConsultStartTime,MINUTE),
    CheckedInTime < AppointmentTime&&OutpatientVisit[VisitStatus]="Consult Start",
    DATEDIFF(CheckedInTime,ConsultStartTime,MINUTE),
    OutpatientVisit[VisitStatus]="Consult Start",
    DATEDIFF(CheckedInTime,ConsultStartTime,MINUTE)
    ,BLANK()
    )

    If it works, please accept it as a solution and your Kudos is much appreciated. 
    Thank you

3 Replies

  • Moetazzahran's avatar
    Moetazzahran
    Resolver II
    2 years ago

    Hello puru85 , 
    You can use this measure to accomedate the additional condition you need.

    WaitTime =
    VAR AppointmentTime =
    MAXX (
    RELATEDTABLE(Appointment),
    Appointment[AppointmentTime]
    )

    VAR CheckedInTime =
    CALCULATE (
    MAX (OutpatientVisit[VisitTime]),
    OutpatientVisit[VisitStatus] = "Checked In",ALLEXCEPT(OutpatientVisit,OutpatientVisit[OupatientIDAndDate],OutpatientVisit[VisitDate])
    )

    VAR ConsultStartTime =
    CALCULATE (
    MAX (OutpatientVisit[VisitTime]),
    OutpatientVisit[VisitStatus] = "Consult Start",ALLEXCEPT(OutpatientVisit,OutpatientVisit[OupatientIDAndDate],OutpatientVisit[VisitDate])
    )


    RETURN
    SWITCH(TRUE(),
    ISBLANK(AppointmentTime),
    DATEDIFF(CheckedInTime,ConsultStartTime,MINUTE),
    CheckedInTime < AppointmentTime,
    DATEDIFF(CheckedInTime,ConsultStartTime,MINUTE),
    DATEDIFF(CheckedInTime,ConsultStartTime,MINUTE)
    )

     


     


    The Highlight for this measure that it can I used 
    ALLEXCEPT(OutpatientVisit,OutpatientVisit[OupatientIDAndDate],OutpatientVisit[VisitDate])
    Which applies the correct filter context for the last condition you required. 

    If this solves your issue, please accept it as a solution. 

    You Kudos will be greatly appreciated.
  • puru85's avatar
    puru85
    Helper II
    2 years ago

    Moetazzahran Thank you very much.

    It works perfectly in measure, but not as calculated column.  When tried in calculated column, the results are not expected. Please help!!

    • Moetazzahran's avatar
      Moetazzahran
      Resolver II
      2 years ago

      Hello puru85 , 
      I believe if you need the desired result you can try this out as a calculated column.

      WaitTime_calcc =
      VAR AppointmentTime =
      MAXX (
      RELATEDTABLE(Appointment),
      Appointment[AppointmentTime]
      )

      VAR CheckedInTime =
      CALCULATE (
      MAX (OutpatientVisit[VisitTime]),
      OutpatientVisit[VisitStatus] = "Checked In",ALLEXCEPT(OutpatientVisit,OutpatientVisit[OupatientID],OutpatientVisit[VisitDate])
      )

      VAR ConsultStartTime =
      CALCULATE (
      MAX (OutpatientVisit[VisitTime]),
      OutpatientVisit[VisitStatus] = "Consult Start",ALLEXCEPT(OutpatientVisit,OutpatientVisit[OupatientID],OutpatientVisit[VisitDate])
      )


      RETURN
      SWITCH(TRUE(),
      ISBLANK(RELATED(Appointment[OupatientIDAndDate]))&&OutpatientVisit[VisitStatus]="Consult Start",
      DATEDIFF(CheckedInTime,ConsultStartTime,MINUTE),
      CheckedInTime < AppointmentTime&&OutpatientVisit[VisitStatus]="Consult Start",
      DATEDIFF(CheckedInTime,ConsultStartTime,MINUTE),
      OutpatientVisit[VisitStatus]="Consult Start",
      DATEDIFF(CheckedInTime,ConsultStartTime,MINUTE)
      ,BLANK()
      )

      If it works, please accept it as a solution and your Kudos is much appreciated. 
      Thank you