Forum Discussion
Find Overlapping Surgeries
Hello,
I have a table with ID, Date, Facility, OR#, Start Time and End Time. I need to find surgeries that are overlapping in the same facility, same OR, as this is a mistake as two surgeries cannot occur at the same time. For example
Record 1 - 9/1/2022, Memorial HOspital, OR #5, Start 7:30 AM, End 9:30 AM
Record 2 - 9/1/2022, Memorial Hospital, OR #5, Start 8:00 AM, End 1100 AM
How could I display these overlapping records?
1. Decide your data granulatity level . Is it half hour slots, 15 minute slots, or minute level slots? What should happen when a surgery goes past midnight? Better use DateTime values.
2. For each surgery generate a list of slots based on 1. (in Power Query)
3. Load the data into Power BI. List all slots with their rowcount. Any rowcount above 1 means there is one or more conflicts for that slot.
Here is an example for half hourly slots.
See attached.
1 Reply
1. Decide your data granulatity level . Is it half hour slots, 15 minute slots, or minute level slots? What should happen when a surgery goes past midnight? Better use DateTime values.
2. For each surgery generate a list of slots based on 1. (in Power Query)
3. Load the data into Power BI. List all slots with their rowcount. Any rowcount above 1 means there is one or more conflicts for that slot.
Here is an example for half hourly slots.
See attached.