Forum Discussion
Matrix as Gantt Chart for Room Utilisation - multiple start and end times
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.
Super UserHey 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=Jh8jrT
Regards,
Tom
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