Calculating Wait Time for Outpatients

[puru85]

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!

[Moetazzahran · Accepted solution]

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]

@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 · Accepted solution]

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

[Moetazzahran · Accepted solution]

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.