Forum Discussion
Help! Calculating Wait time
Hi Experts,
Attached is my PBIX with Datamodel.
Hospital_Outpatients_Waittime.pbix
I am finding it challenging to achieve the correct results for wait time based on the following logic:
Wait Time Calculation Logic:
Wait Time =
IF (
'Invoice_statushis'[Created_on_Time] (Status = 'Checked In') < 'Appointment'[Start_time],
'Invoice_statushis'[Created_on_Time](Status = 'Consult Start') - Appointment.AppointmentTime,
'Invoice_statushis'[Created_on_Time](Status = 'Consult Start') - Invoice_statushis'[Created_on_Time](Status = 'Checked In')
)
Anyone, Please Help!!
Try this and see
Add this column to your appointment table!
Column Wait Times = -- Get the invoice id of the same patient id and same date var _inv_pid_appt2 = CALCULATE( MAX(Invoice[_id]), FILTER( ALLSELECTED(Invoice), Invoice[patient_id] = appointment[patient_id] && Invoice[date].[Date] = appointment[Start_time].[Date]) ) -- Get the checked in record time of the same invoice id var _inv_statushis_pid_appt2_checkedIn = CALCULATE( MAX(Invoice_statushis[Created_on_Time]), FILTER( ALL(Invoice_statushis), Invoice_statushis[invoice_id] = _inv_pid_appt2 && Invoice_statushis[Status] = "Checked In") ) -- Get the consult start record time of the same invoice id var _inv_statushis_pid_appt2_consultin = CALCULATE( MAX(Invoice_statushis[Created_on_Time]), FILTER( ALL(Invoice_statushis), Invoice_statushis[invoice_id] = _inv_pid_appt2 && Invoice_statushis[Status] = "Consult Start") ) RETURN IF ( _inv_statushis_pid_appt2_checkedIn < appointment[Start_time], DATEDIFF(_inv_statushis_pid_appt2_consultin, appointment[Start_time], MINUTE), DATEDIFF( _inv_statushis_pid_appt2_consultin, _inv_statushis_pid_appt2_checkedIn, MINUTE) )First, I tried getting the invoice id for the same patient id and patient appointment date.
Second, I used the invoice id and then filter the checked in and consult start events and get the max time
Third and last step, tried to do your logic calculation. You may have to tune further this step as it does not make sense purely if statement vs true condition calc. My thinking says as
_inv_statushis_pid_appt2_consultin < appointment[Start_time]
Tune to your needs and see if this helps!
3 Replies
Try this and see
Add this column to your appointment table!
Column Wait Times = -- Get the invoice id of the same patient id and same date var _inv_pid_appt2 = CALCULATE( MAX(Invoice[_id]), FILTER( ALLSELECTED(Invoice), Invoice[patient_id] = appointment[patient_id] && Invoice[date].[Date] = appointment[Start_time].[Date]) ) -- Get the checked in record time of the same invoice id var _inv_statushis_pid_appt2_checkedIn = CALCULATE( MAX(Invoice_statushis[Created_on_Time]), FILTER( ALL(Invoice_statushis), Invoice_statushis[invoice_id] = _inv_pid_appt2 && Invoice_statushis[Status] = "Checked In") ) -- Get the consult start record time of the same invoice id var _inv_statushis_pid_appt2_consultin = CALCULATE( MAX(Invoice_statushis[Created_on_Time]), FILTER( ALL(Invoice_statushis), Invoice_statushis[invoice_id] = _inv_pid_appt2 && Invoice_statushis[Status] = "Consult Start") ) RETURN IF ( _inv_statushis_pid_appt2_checkedIn < appointment[Start_time], DATEDIFF(_inv_statushis_pid_appt2_consultin, appointment[Start_time], MINUTE), DATEDIFF( _inv_statushis_pid_appt2_consultin, _inv_statushis_pid_appt2_checkedIn, MINUTE) )First, I tried getting the invoice id for the same patient id and patient appointment date.
Second, I used the invoice id and then filter the checked in and consult start events and get the max time
Third and last step, tried to do your logic calculation. You may have to tune further this step as it does not make sense purely if statement vs true condition calc. My thinking says as
_inv_statushis_pid_appt2_consultin < appointment[Start_time]
Tune to your needs and see if this helps!sevenhills
Thank you for your great help!! Much appreciated.Glad to hear it helped!
