cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors