The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I am trying to write a function which calculates the time from when one case in an OR finishes and the next case starts. Attached is a sample spreadsheet which lists an OR resource, the start and stop times for a case and the surgeon. I need to automatically calculate the time from when a surgeon completes one case until the next case starts (turnover time). Data are sorted by OR resource first and then time/date from earliest to latest.
For a beginning in DAX formulas, this is a very difficult problem for me, though it is conceptually simple. Any help is very greatly appreciated! Below is a link to my sample data.
https://1drv.ms/x/s!AodJ9zqlTT5LqoM9ba4tzmrRABdigw?e=vi6cqE
Best!
Dave
@davidv43 , Based on what I got so far. Based on each OR resource
a new column
new column =
var _max = maxX(filter(Table, [OR Resource] =earlier([OR Resource]) && [Patient in room] <earlier([Patient in room])),[Patient Out room])
return
datediff(_max, [Patient in room], second)
For the most part, this works, although I don't understand the "earlier" syntax. The MIcrosoft DAX guide says:
PARAMETER ATTRIBUTES DESCRIPTION
ColumnName | The column that contains the desired value. | |
Number | Optional | The number of table scan. |
The current value of row, from ColumnName, at Number of outer evaluation passes.
Also, I need to make sure that when the day changes, the turnoever time is not calculated for the first case of the day.
I have a lot to learn about DAX! Thank you for this help!
User | Count |
---|---|
27 | |
12 | |
8 | |
8 | |
5 |
User | Count |
---|---|
31 | |
15 | |
12 | |
7 | |
7 |