cancel
Showing results 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.

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

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

Power BI Monthly Update - April 2024

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

Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors