cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

New Member

## Room utilization

I have a problem which I can solve without too much difficulty in Excel, but since Excel's formulas are row-based and DAX is not, I am struggling with how to solve this in DAX or PowerQuery.

The situation is that we have operating rooms which are used from 7am until 5 pm. That is, there are 600 minutes available for use by surgeons. I am trying to calculate two things. First is the turnover time which is defined as the number of minutes from the time one patient leaves the room until the next patient is in the room. Assume that one surgeon has one room per day.  The second question is what percentage of the 600 minutes which are available are actually used during the day by one surgeon?

In the attached spreadsheet, I provided a sheet with Raw Data and then a sheet with finished data, using excel functions to calculate the various outcomes I desire (columns "Turnover Time", "Block efficiency" and "Block utilization". Block efficiency and block utilization are essentially the same except that block utilization includes the turnover time.

Eventually, I want to produce a KPI which shows the Block Utilization, mean turnover time and Block Efficiency for each surgeon filtered by a date slicer.

I would appreciate any help on solving this using DAX or PowerQuery.

Attached is my data.

https://1drv.ms/x/s!AodJ9zqlTT5LsZlfTW1dZ1za7Fw6Ng?e=VE3q92

1 ACCEPTED SOLUTION
Super User

See attached. Note that your sample data has DQ issues.

4 REPLIES 4
New Member

Thank you very much!

New Member

Thank you for your time. Your solution will take me a little while to make sense of. May I ask what are DQ issues?  Also, I need to sort these data by surgeon and by month.

Super User

DQ = Data Quality.  You have duplicate entries for 2/21.

Super User

See attached. Note that your sample data has DQ issues.

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors