Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
aashton
Helper V
Helper V

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 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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.

 

lbendlin_0-1665263471714.png

 

 

See attached.

View solution in original post

1 REPLY 1
lbendlin
Super User
Super User

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.

 

lbendlin_0-1665263471714.png

 

 

See attached.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.