Forum Discussion
Matrix as Gantt Chart for Room Utilisation - multiple start and end times
Hi All
I've been searching the community and indeed the internet for a few weeks now and I still can't find what I'm looking for.
I have a requirement to show utilisation of an operating theatre per date.
I created a time gantt chart using the matrix visualisation incrementing by 15 minute slots and showed the utilisation of a room based on the earliest 'into theatre' time of procedures performed on a specific date and the latest 'out of theatre' time of the procedures performed.
I managed to achieve this easily enough with the below code and conditional formatting.
Step 1 =
var CathlabStartTime =
CALCULATE(
MIN('cathlab theatre events'[Rounded Earliest Time]),
REMOVEFILTERS(dim_time[time])
)
var CathlabEndTime =
CALCULATE(
MAX('cathlab theatre events'[Rounded Latest Time]),
REMOVEFILTERS(dim_time[time])
)
var CathlabUtilisation =
MIN(dim_time[Rounded Time]) >= CathlabStartTime
&& MAX(dim_time[Rounded Time]) <= CathlabEndTime
var Result =
IF(
CathlabUtilisation,
1,0
)
RETURN
Result
Now I'd like to show the utilisation of the theatre but use each individual cases start and finish time and see each row have multiple sections coloured where the room was in use based on these start and finish times. Below is a sample dataset and how I'd like the visual to look.
| Theatre Event Key | Surgery Date | Operating Theatre | Start | Finish | Duration |
| 1 | 14/07/2022 | OR1 | 07:30:00 | 08:30:00 | 1:00:00 |
| 2 | 14/07/2022 | OR1 | 09:15:00 | 12:15:00 | 3:00:00 |
| 3 | 14/07/2022 | OR1 | 13:30:00 | 14:30:00 | 1:00:00 |
| 4 | 14/07/2022 | OR1 | 14:30:00 | 14:45:00 | 0:15:00 |
| 5 | 14/07/2022 | OR1 | 15:00:00 | 15:30:00 | 0:30:00 |
| 6 | 14/07/2022 | OR1 | 15:45:00 | 16:15:00 | 0:30:00 |
| 7 | 14/07/2022 | OR1 | 16:45:00 | 17:30:00 | 0:45:00 |
| 8 | 14/07/2022 | OR2 | 07:30:00 | 10:15:00 | 2:45:00 |
| 9 | 14/07/2022 | OR2 | 10:30:00 | 11:30:00 | 1:00:00 |
| 10 | 14/07/2022 | OR2 | 12:45:00 | 13:30:00 | 0:45:00 |
| 11 | 14/07/2022 | OR2 | 13:30:00 | 14:30:00 | 1:00:00 |
| 12 | 14/07/2022 | OR2 | 15:15:00 | 16:15:00 | 1:00:00 |
| 13 | 14/07/2022 | OR2 | 17:30:00 | 18:00:00 | 0:30:00 |
I don't have a relationship between my time dimension and my theatre events fact table but when I do create that relationship and change nothing else my visual actually changes to show the start of each case so I'm sure it's possible ... I just don't know how to bring it about 😄
With the relationship between dim_time and 'cathlab theatre events' tables
I appreciate any and all help that anyone can lend. I will attempt anything and have even started teaching myself R but that will take some time.
Thanks
Ciara
Hi Anonymous ,
You should be able to do this with a change or two to the measure you have currently. I've created a rough setup but the idea should work the same.
Keep your time table unlinked, and create a measure like this one
Measure = Var _time = SELECTEDVALUE('Time Table'[Time]) Var _activity = CALCULATE(COUNTROWS('Table'), 'Table'[Start]<= _time , 'Table'[Finish] > _time) Return _activity + 0The idea is to look at the current time, then count how many events are active at that time, using CALCULATE to filter the table.
If you add the 'Operating Theatre' field into you rows, turn off 'stepped layout' under row headers formatting and then add this measure to values
That will give you something like this
And then you can format the 1s to show colour and the 0s to be blank, as you have before.
Hope that helps,
Any problems please let me know.
6 Replies
- TomMartens
Super User
Hey Anonymous ,
here is a different approach to the one provided by AntonioM .
As you are facing the event-in-progress challenge (read here: Events-In-Progress – Gerhard Brueckl on BI & Data (gbrueckl.at))
For quite some time now, I do not use DAX to tackle this kind of challenge, instead I create an "expanded" fact table that contains a row for each point described by the start and finish column. the following screenshot shows an excerpt of the expanded table based on the sample data you provided:
Please be aware that the I changed the datatype of the column Duration to duration (only Power Query does know this data type the dataset does not know this data type.
Then I use Power Query to- determine the number of quarters
- create a list
- expand this list to rows
- create a new column called "Occupied Quarter". I created this column using an line-function, this may seem weird but is very efficient (you will what I'm talking about if you open the Advanced editor in Power Query)
- finally I creaed another column with a simple value, this value marks the room as occupied
You can relate your time table to the new column "OccupiedQuarter".
This is possible without any DAX, except for the conditional formatting:
Depending on the nature of the existing fact table, and the numeric values, I expand the existing table or create an additional table.
From my experience this approach is more efficient than the DAX approach, of course, it consumes additional RAM.
Here you will find a little pbix file: https://tommartens-my.sharepoint.com/:u:/g/personal/tom_minceddata_com/EVpQowE8z3tHlTp6ksx6MDcBKFeMz3qdcInIibt4eRPXjA?e=Jh8jrTRegards,
TomHi TomMartens ,
I am trying to replicate your solution for my own dataset, with the key difference that the "Duration" field you have is a calculated field for me in Power Query, which is essentially the difference between the "Start" & "Finish" Time.
When I was creating a new Column for the "NoOfQuarters", I experienced the following error code:
Expression.Error: The number is out of range of a 32 bit integer value.
Details:
2.983333333Would you kindly advise on this please? Many thanks!
- TomMartens
Hey elendil5259 ,
please provide a pbix that helps to reproduce the isue you are facing. Make sure that the pbix contains sample data but still relects your semantic model (tables, relationships, calculated columns, and measures). Upload the pbix to OneDrive, Google Drive, or dropbox and share the link. If you are using Excel to create the sample data instead of the manual input method share the spreadsheet as well.
Thanks,
Tom
- AntonioM
Solution Sage
Hi Anonymous ,
You should be able to do this with a change or two to the measure you have currently. I've created a rough setup but the idea should work the same.
Keep your time table unlinked, and create a measure like this one
Measure = Var _time = SELECTEDVALUE('Time Table'[Time]) Var _activity = CALCULATE(COUNTROWS('Table'), 'Table'[Start]<= _time , 'Table'[Finish] > _time) Return _activity + 0The idea is to look at the current time, then count how many events are active at that time, using CALCULATE to filter the table.
If you add the 'Operating Theatre' field into you rows, turn off 'stepped layout' under row headers formatting and then add this measure to values
That will give you something like this
And then you can format the 1s to show colour and the 0s to be blank, as you have before.
Hope that helps,
Any problems please let me know.
Hi TomMartens and AntonioM
Thank you both so much for your responses. Antonio I used your code and it worked beautifully (it's so easy when you know how hey? 😄) Thank you so much.
Tom, I've to do a little more figuring out with your code in order to implement it but the article you sent is very informative and the pbix will be a great help. I will be trying this out also.
Thanks to you both again.
Ciara
Hi there,
I have a similar problem and would like to understand your initial solution in detail.Could you explain a little more about the individual measures/columns/data fields you created?
In particular:
- dim_time[time]
- cathlab theatre events'[Rounded Earliest Time]
Thank you!
