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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.