Matrix as Gantt Chart for Room Utilisation - multiple start and end times

[Anonymous]

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

[elendil5259]

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!

[Anonymous]

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

[TomMartens]

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

1. determine the number of quarters
2. create a list
3. expand this list to rows
4. 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)
5. 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=Jh8jrT

Regards,
Tom

[elendil5259]

Hi @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.983333333

Would 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 · Accepted solution]

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 + 0

The 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.