Theatre utilisation semantic model

A theatre list is a block of planned time, and every minute of it either has a patient in theatre or is lost to a late start, a turnaround between patients or an early finish. This project is the semantic model I’d build to report that across a trust’s sites, written in TMDL so every table, measure and role can be reviewed in Git. It uses the features my referral-to-treatment waiting list semantic model didn’t need: a calculation group for time comparisons with dynamic format strings, a field parameter that lets readers switch the measure in a chart, and row-level security by site.

The data is synthetic: 6,844 theatre lists and 35,014 booked cases at three invented sites of an invented trust, from April 2024 to September 2025. A Python script generates it, writes the CSVs the model loads, and computes every measure and calculation item in pandas with the same definitions as the DAX, so the charts below show what the report would show. I haven’t opened the model in Power BI Desktop: the TMDL loads in Microsoft’s serializer, and every number on this page comes from the Python code, not from Power BI.

uv run python projects/theatre-utilisation-model/run.py

In short: the main hospital’s start-time programme cut late starts from 44% of lists to 16%, but its touch-time utilisation rose only 2.5 points: just under half the time recovered from late starts went to early finishes and turnaround instead. The elective centre comes closest to GIRFT’s 85%, at 78.0% over the last twelve months. Turnaround between patients is the biggest loss at every site.

What the report has to answer

Every month a theatre report is asked how much planned list time went on patients and where the rest went, how many patients were cancelled on the day and why, and how that compares with last year and the financial year so far. Each site’s managers want the same for their own site.

The definitions drive every number:

• Touch time runs from the start of anaesthesia to the patient leaving theatre.
• Touch-time utilisation is touch time inside the planned list over planned list minutes. It is capped: time after the planned end doesn’t count, so an overrun can’t hide a late start. GIRFT’s theatre productivity programme targets a minimum of 85% capped theatre utilisation.
• Session utilisation is the planned time from the first anaesthetic start to the last patient out, over planned minutes. The gap between the two utilisations is turnaround.
• A late start is a first case starting more than 15 minutes after the planned start. Trusts set this threshold differently; here it’s one constant.
• On-the-day cancellation rate is cases cancelled on the day over cases booked on lists that ran.

The data

The generator walks through every day from 1 April 2024. Eleven theatres at a main hospital, an elective centre and a community hospital follow a weekly timetable of morning, afternoon and all-day lists. Each list is booked with cases until the expected minutes reach a target, then played out: the first case starts on time or late, the rest follow after a turnaround, some patients are cancelled on the day, and a case that can’t finish within 30 minutes of the planned end is cancelled because the list ran out of time.

The patterns are planted, and the README in the code download gives every constant:

• Monday mornings start late more often, at every site.
• The main hospital runs a start-time programme from January 2025, fully in place by April.
• The elective centre turns round in about half the time of the other two. The community hospital underbooks its lists and has more patients who don’t attend.
• The main hospital runs short of beds in winter and on Mondays.
• Specialties differ: a completed ophthalmology case takes a median 17 minutes, a trauma and orthopaedics case 93.

The model design

Nine single-direction, many-to-one relationships join the two facts to the dimensions they share. The facts aren’t joined to each other. Planned minutes belong to lists; touch time and cancellations belong to cases; they meet through Date, Site, Theatre and Specialty.

The minutes are split before they reach the model. Inside a list’s planned window every minute is exactly one of late start, touch time, turnaround or early finish, so the measures are plain sums, and run.py checks that the four add up to the planned minutes on all 6,844 lists after writing the CSVs. The two utilisations:

Touch-time Utilisation = DIVIDE ( [Touch Minutes In Session], [Planned Minutes] )

Session Utilisation =
DIVIDE (
    [Planned Minutes] - [Late Start Minutes] - [Early-finish Minutes],
    [Planned Minutes]
)

model.tmdl sets discourageImplicitMeasures, because calculation items only apply to explicit measures.

A calculation group for time comparisons

Five time comparisons of 17 measures would be 85 more measures. The Time Intelligence calculation group holds them once, as items of a Period column: Current, PY, PY Change, PY Change %, FYTD and Rolling 12M. PY Change has a dynamic format string that puts a sign on whatever format the measure already has:

		/// Current minus PY, shown with a sign in the measure's own format. For a rate this is a change in percentage points.
		calculationItem 'PY Change' =
				VAR CurrentValue = SELECTEDMEASURE ()
				VAR PriorValue =
					CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
				RETURN
					IF ( NOT ISBLANK ( CurrentValue ) && NOT ISBLANK ( PriorValue ), CurrentValue - PriorValue )

			formatStringDefinition =
					VAR MeasureFormat = SELECTEDMEASUREFORMATSTRING ()
					RETURN
						"+" & MeasureFormat & ";-" & MeasureFormat & ";" & MeasureFormat

A count shows as +2,354 and a rate as +0.5%, which for a rate is a change in points. A percentage change of a rate would mislead, so PY Change % uses ISSELECTEDMEASURE to return blank for the four rate measures, with a fixed format string of +0.0%;-0.0%;0.0%.

FYTD needs a 31 March year end, and a guard so the months after the data stops stay blank:

		/// Financial year to date, April to March. The year in the year-end string is ignored. Blank after the last date with data.
		calculationItem FYTD =
				VAR LastDataDate =
					CALCULATE ( MAX ( Sessions[Session Date] ), REMOVEFILTERS () )
				RETURN
					IF (
						MIN ( 'Date'[Date] ) <= LastDataDate,
						CALCULATE ( SELECTEDMEASURE (), DATESYTD ( 'Date'[Date], "2025-03-31" ) )
					)

Rolling 12M stays blank until twelve whole months of data exist, so it starts in March 2025 instead of showing a two-month “rolling year” in May 2024. The guard works out where a full window would start, EDATE ( LastVisible, -12 ) + 1, and blanks the item if that falls before the first month of data. The obvious alternative, checking the first date of the DATESINPERIOD result, doesn’t work: DATESINPERIOD returns only dates that exist in the Date table, which starts on 1 April 2024, so a short window would pass the check. The pandas version and the trend chart below apply the same rule.

Loading the files with Microsoft’s serializer taught me two things about calculation groups in TMDL. The order of the calculationItem blocks sets the ordinal: I wrote ordinal properties, the serializer read them and left them out when it wrote the model back, and reloading its output gave the same order. And the calculation group needs no partition in the file; the loaded model has one anyway.

For September 2025 the items give this touch-time utilisation, as pandas computes it and as the second query in the .dax download should return:

One month against the same month last year is noisy. The main hospital’s September was only half a point up, while its financial year to date is 71.2% against 68.9% for the whole of 2024/25. The same item on early-finish minutes shows where the recovered time went: the main hospital’s September had 2,354 more minutes of early finishes than a year before, +55.3%.

A field parameter to switch the measure

A slicer on Theatre Measure changes which measure a chart shows. Power BI Desktop builds the table from New parameter > Fields as a calculated table of display names, NAMEOF references and an order:

	partition 'Theatre Measure' = calculated
		mode: import
		source =
				{
					("Touch-time utilisation", NAMEOF ( 'Sessions'[Touch-time Utilisation] ), 0),
					("Session utilisation", NAMEOF ( 'Sessions'[Session Utilisation] ), 1),
					("Late-start rate", NAMEOF ( 'Sessions'[Late-start Rate] ), 2),
					("Early-finish minutes", NAMEOF ( 'Sessions'[Early-finish Minutes] ), 3),
					("Cases per session", NAMEOF ( 'Cases'[Cases per Session] ), 4),
					("On-the-day cancellation rate", NAMEOF ( 'Cases'[On-the-day Cancellation Rate] ), 5)
				}

The DAX isn’t what makes it a field parameter. SQLBI found that Power BI looks for an extended property called ParameterMetadata on the hidden Fields column, and for the display column being grouped by it. SQLBI noted in 2022 that this isn’t documented, so I copied the exact form from a sample PBIP in Microsoft’s Analysis Services repository on GitHub, and the TOM library loads it:

	column 'Theatre Measure'
		summarizeBy: none
		sourceColumn: [Value1]
		sortByColumn: 'Theatre Measure Order'

		relatedColumnDetails
			groupByColumn: 'Theatre Measure Fields'

	column 'Theatre Measure Fields'
		isHidden
		summarizeBy: none
		sourceColumn: [Value2]
		sortByColumn: 'Theatre Measure Order'

		extendedProperty ParameterMetadata =
				{
				  "version": 3,
				  "kind": 2
				}

It combines with the calculation group because the query a visual sends names the chosen fields directly, as SQLBI’s traces of field parameters show. The calculation item then applies to that measure like any other, and the PY Change format string follows whichever one is chosen.

Row-level security by site

Three static roles each filter to one site. A fourth, dynamic role reads a hidden mapping table of user principal names and sites:

/// Static role: lists, cases and theatres at the main hospital only.
role 'Main Hospital'
	modelPermission: read

	tablePermission Site = Site[Site Code] = "MAIN"

	tablePermission Theatre = Theatre[Site Code] = "MAIN"

	tablePermission 'Site Access' = FALSE ()

/// Dynamic role: each member sees the sites listed against their user principal name in Site Access. A member who isn't listed sees no lists or cases.
role 'Site Managers'
	modelPermission: read

	tablePermission Site =
			Site[Site Key]
				IN CALCULATETABLE (
					VALUES ( 'Site Access'[Site Key] ),
					'Site Access'[User Principal Name] = USERPRINCIPALNAME ()
				)

	tablePermission Theatre =
			Theatre[Site Key]
				IN CALCULATETABLE (
					VALUES ( 'Site Access'[Site Key] ),
					'Site Access'[User Principal Name] = USERPRINCIPALNAME ()
				)

	tablePermission 'Site Access' = 'Site Access'[User Principal Name] = USERPRINCIPALNAME ()

A filter on Site reaches both facts through their relationships, but not Theatre, which has no relationship to Site. So every role filters Theatre as well, or other sites’ theatres would still appear in a slicer. Every role also filters Site Access, so nobody can list who else has access. This is what each role sees, from the pandas version of the same filters:

Row-level security for shared models goes through the design choices and what RLS doesn’t protect.

Results

Over the last twelve months the elective centre leads in every specialty it runs. Its trauma and orthopaedics lists reach 81.3% touch-time utilisation, the highest in the trust. Switch to session utilisation and ophthalmology moves from near the bottom to the top: its cases take a median 17 minutes, so turnarounds are a large share of a busy list. Over the whole period ophthalmology loses 22.2% of planned time to turnaround, against 10.9% for trauma and orthopaedics. That gap is why the model has both measures, and why the field parameter lets a reader swap between them.

Data table

Touch-time utilisation

Specialty	Main hospital	Elective centre	Community hospital
General Surgery	70.1%	78.8%	67.0%
Trauma & Orthopaedics	72.4%	81.3%
Urology	67.0%		66.0%
Gynaecology	68.1%		65.4%
ENT	68.3%	79.0%	67.8%
Ophthalmology		73.9%	59.0%

Session utilisation

Specialty	Main hospital	Elective centre	Community hospital
General Surgery	82.5%	87.0%	77.8%
Trauma & Orthopaedics	86.0%	89.5%
Urology	84.8%		82.4%
Gynaecology	85.3%		80.5%
ENT	83.1%	88.3%	80.9%
Ophthalmology		94.5%	85.6%

Synthetic data. Source: projects/theatre-utilisation-model.

Before the programme, 70% of the main hospital’s Monday morning lists started more than 15 minutes late; between April and September 2025, 25% did. Every site keeps a Monday morning peak: over the whole period, 54% of the main hospital’s Monday morning lists started late against 34% to 41% on other mornings, and the community hospital shows the same pattern at 33% against 19% to 22%.

Data table

April to December 2024

List start and site	Mon	Tue	Wed	Thu	Fri
AM Main hospital	70%	50%	54%	43%	56%
AM Elective centre	22%	10%	14%	12%	12%
AM Community hospital	32%	21%	17%	25%	20%
PM Main hospital	31%	29%	25%	28%	29%
PM Elective centre	6%	6%	9%	10%	10%
PM Community hospital	12%	10%	13%	20%	8%

April to September 2025

List start and site	Mon	Tue	Wed	Thu	Fri
AM Main hospital	25%	13%	20%	21%	21%
AM Elective centre	20%	5%	18%	12%	13%
AM Community hospital	24%	17%	24%	16%	17%
PM Main hospital	17%	14%	15%	3%	4%
PM Elective centre	6%	12%	1%	8%	2%
PM Community hospital	17%	12%	16%	14%	17%

Synthetic data. Source: projects/theatre-utilisation-model.

The monthly line moves a point or two either way. Switch to financial year to date or rolling 12 months, two items of the same calculation group, and the main hospital’s step up after the programme is clearer: its rolling twelve months rose from 68.9% in March 2025 to 70.1% in September.

Data table

Month (Current)

Month	Main hospital	Elective centre	Community hospital
2024-04-01	67.8%	77.5%	62.6%
2024-05-01	67.7%	78.3%	65.4%
2024-06-01	69.3%	78.7%	64.0%
2024-07-01	68.1%	78.9%	63.9%
2024-08-01	70.6%	76.8%	63.5%
2024-09-01	69.7%	76.7%	64.4%
2024-10-01	67.7%	78.9%	64.5%
2024-11-01	69.2%	79.1%	64.7%
2024-12-01	68.3%	76.6%	63.7%
2025-01-01	69.7%	76.7%	62.5%
2025-02-01	68.2%	78.4%	64.7%
2025-03-01	70.5%	78.0%	62.8%
2025-04-01	71.6%	79.0%	66.3%
2025-05-01	72.8%	78.0%	63.9%
2025-06-01	72.2%	78.6%	64.1%
2025-07-01	70.8%	76.9%	63.0%
2025-08-01	69.5%	77.9%	66.2%
2025-09-01	70.2%	77.4%	63.1%

Financial year to date (FYTD)

Month	Main hospital	Elective centre	Community hospital
2024-04-01	67.8%	77.5%	62.6%
2024-05-01	67.8%	77.9%	64.0%
2024-06-01	68.3%	78.1%	64.0%
2024-07-01	68.2%	78.3%	64.0%
2024-08-01	68.7%	78.1%	63.9%
2024-09-01	68.8%	77.8%	64.0%
2024-10-01	68.7%	78.0%	64.0%
2024-11-01	68.7%	78.1%	64.1%
2024-12-01	68.7%	78.0%	64.1%
2025-01-01	68.8%	77.9%	63.9%
2025-02-01	68.8%	77.9%	64.0%
2025-03-01	68.9%	77.9%	63.9%
2025-04-01	71.6%	79.0%	66.3%
2025-05-01	72.2%	78.5%	65.1%
2025-06-01	72.2%	78.5%	64.7%
2025-07-01	71.8%	78.1%	64.3%
2025-08-01	71.4%	78.1%	64.6%
2025-09-01	71.2%	77.9%	64.3%

Rolling 12 months (Rolling 12M)

Month	Main hospital	Elective centre	Community hospital
2025-03-01	68.9%	77.9%	63.9%
2025-04-01	69.2%	78.0%	64.2%
2025-05-01	69.6%	78.0%	64.1%
2025-06-01	69.9%	78.0%	64.1%
2025-07-01	70.1%	77.8%	64.0%
2025-08-01	70.0%	77.9%	64.2%
2025-09-01	70.1%	78.0%	64.1%

Synthetic data. Source: projects/theatre-utilisation-model.

Across all sites and all 18 months, 14.1% of planned time went on turnaround, 10.1% on early finishes and 4.0% on late starts; 27,582 minutes of overruns didn’t count. At the main hospital, late starts fell from 7.6% of planned time (April to December 2024) to 2.7% (April to September 2025). Touch time took 2.5 of those 4.9 points; early finishes rose from 10.2% to 11.4% and turnaround from 13.6% to 14.6%. With the same cases booked, a list that starts on time has more room to finish early, so the rest of the recovered time becomes touch time only if booking changes as well.

Data table

All sites

Month	Late start	Turnaround	Early finish, list had a cancellation	Other early finish
Apr 2024	4.6%	13.7%	5.8%	5.2%
May 2024	5.2%	13.6%	5.0%	5.0%
Jun 2024	4.7%	13.5%	4.8%	5.2%
Jul 2024	4.9%	13.6%	4.9%	5.1%
Aug 2024	4.2%	13.8%	5.1%	5.2%
Sep 2024	4.9%	14.1%	3.9%	5.7%
Oct 2024	5.1%	14.0%	4.7%	4.8%
Nov 2024	4.8%	14.0%	4.0%	5.0%
Dec 2024	5.4%	14.0%	4.3%	5.5%
Jan 2025	4.4%	14.6%	4.7%	5.3%
Feb 2025	4.0%	13.4%	5.5%	5.7%
Mar 2025	3.6%	14.3%	4.8%	5.4%
Apr 2025	2.7%	14.6%	3.3%	6.0%
May 2025	2.8%	14.1%	4.5%	5.5%
Jun 2025	2.6%	14.4%	3.9%	6.0%
Jul 2025	2.6%	14.8%	5.0%	5.8%
Aug 2025	2.2%	15.0%	4.5%	6.3%
Sep 2025	3.2%	14.2%	5.0%	6.0%

Main hospital

Month	Late start	Turnaround	Early finish, list had a cancellation	Other early finish
Apr 2024	7.2%	13.5%	7.1%	4.3%
May 2024	8.3%	13.4%	7.0%	3.6%
Jun 2024	7.6%	12.8%	5.9%	4.4%
Jul 2024	7.8%	13.2%	7.2%	3.7%
Aug 2024	5.5%	14.0%	5.2%	4.7%
Sep 2024	7.7%	13.8%	4.8%	3.9%
Oct 2024	7.9%	13.8%	7.2%	3.4%
Nov 2024	7.6%	14.2%	4.6%	4.3%
Dec 2024	8.6%	13.1%	6.0%	4.0%
Jan 2025	6.6%	13.9%	5.8%	4.0%
Feb 2025	5.0%	13.3%	8.3%	5.1%
Mar 2025	4.0%	14.3%	6.4%	4.8%
Apr 2025	2.7%	14.9%	4.0%	6.8%
May 2025	2.7%	14.5%	4.5%	5.5%
Jun 2025	2.6%	14.5%	4.9%	5.8%
Jul 2025	2.6%	15.1%	5.5%	5.9%
Aug 2025	2.2%	15.2%	6.9%	6.3%
Sep 2025	3.3%	13.8%	6.1%	6.5%

Elective centre

Month	Late start	Turnaround	Early finish, list had a cancellation	Other early finish
Apr 2024	2.2%	12.3%	4.1%	3.9%
May 2024	2.3%	12.0%	3.0%	4.4%
Jun 2024	1.8%	12.1%	3.7%	3.7%
Jul 2024	1.8%	12.2%	2.7%	4.5%
Aug 2024	2.6%	11.7%	4.6%	4.3%
Sep 2024	2.4%	12.7%	3.4%	4.7%
Oct 2024	2.2%	12.5%	1.9%	4.6%
Nov 2024	2.2%	12.1%	3.1%	3.6%
Dec 2024	2.5%	12.6%	3.3%	5.0%
Jan 2025	1.7%	13.1%	3.7%	4.8%
Feb 2025	2.3%	11.9%	3.3%	4.0%
Mar 2025	2.7%	12.6%	3.0%	3.7%
Apr 2025	2.1%	12.3%	2.7%	3.9%
May 2025	2.4%	12.2%	3.8%	3.6%
Jun 2025	2.0%	12.3%	2.2%	4.9%
Jul 2025	1.9%	12.8%	4.5%	3.9%
Aug 2025	1.8%	13.8%	3.1%	3.3%
Sep 2025	2.4%	12.6%	3.6%	4.0%

Community hospital

Month	Late start	Turnaround	Early finish, list had a cancellation	Other early finish
Apr 2024	3.3%	17.4%	6.4%	10.3%
May 2024	3.6%	17.5%	4.0%	9.5%
Jun 2024	3.8%	17.8%	4.2%	10.2%
Jul 2024	4.3%	17.6%	4.0%	10.2%
Aug 2024	4.0%	17.9%	6.0%	8.6%
Sep 2024	2.9%	17.7%	3.0%	12.0%
Oct 2024	4.4%	17.6%	4.7%	8.8%
Nov 2024	3.3%	17.8%	4.3%	10.0%
Dec 2024	4.1%	19.6%	2.3%	10.4%
Jan 2025	4.5%	19.4%	3.8%	9.9%
Feb 2025	4.8%	16.7%	3.1%	10.7%
Mar 2025	4.2%	18.0%	4.4%	10.6%
Apr 2025	3.9%	18.5%	2.8%	8.6%
May 2025	3.7%	17.1%	5.8%	9.5%
Jun 2025	3.7%	18.7%	4.8%	8.6%
Jul 2025	4.2%	18.2%	4.9%	9.6%
Aug 2025	2.9%	17.2%	1.2%	12.4%
Sep 2025	4.5%	18.3%	5.0%	9.1%

Synthetic data. Source: projects/theatre-utilisation-model.

The programme’s clearest gain was in cancellations. At the main hospital, patients cancelled because the list ran out of time fell from 6.0% of booked cases to 2.4%, and the on-the-day cancellation rate from 10.8% to 7.1%. Over the whole period the main hospital also lost 1.5% of booked cases for want of a bed, and the community hospital 1.8% to patients who didn’t attend.

Data table

Reason	Main hospital	Elective centre	Community hospital
List ran out of time	4.4%	1.3%	0.6%
Patient unwell	1.0%	0.9%	0.9%
Patient did not attend	0.7%	0.7%	1.8%
Not fit for surgery	0.9%	0.9%	0.9%
No bed available	1.5%	0.2%	0.0%
Patient declined on the day	0.4%	0.4%	0.3%
Equipment or theatre fault	0.3%	0.3%	0.3%
Staff unavailable	0.2%	0.2%	0.2%

Synthetic data. Source: projects/theatre-utilisation-model.

What was tested, and what wasn’t

• TMDL structure: tested. A small .NET program loads the folder with TmdlSerializer from Microsoft’s Analysis Services client library (the Tabular Object Model) and reports 10 tables, 17 measures, 6 calculation items, 9 relationships and 4 roles. I checked that loading fails for a role naming a missing table, a field parameter grouped by a missing column and a made-up calculation group property. Serialising the loaded model back out reproduces the files byte for byte.
• DAX syntax: tested. Every measure, calculation item, format string expression, role filter, the field parameter table and the full query file parse in DAX Formatter. That checks syntax, not names or results.
• Measure logic: tested twice, outside DAX. Each measure and calculation item has a pandas version, and SQLite recomputes the six headline measures by site from the CSVs. run.py stops if they disagree.
• Not tested: opening the model in Power BI Desktop, refreshing, evaluating the DAX, the field parameter in a real slicer and the roles under Modeling > View as. I didn’t have Power BI Desktop or an Analysis Services engine to run them on. The README explains how to open the model and what the DAX queries should return.

Limits

• The lists and cases come from a simulation, not from any real trust. The shapes are plausible; the levels aren’t calibrated to anything.
• Touch time here is a single interval per case. Real systems record anaesthetic start, knife to skin, end of surgery and out of theatre separately, and have missing and out-of-order timestamps. This data has neither.
• Lists released in advance aren’t in the data, so the model can’t report unused lists, which is often the biggest loss of all.
• The static roles and the dynamic role are both here to compare them. A real model would ship one approach; with both, a user placed in two roles sees the union of their sites.

What I’d do next

• Open it in Power BI Desktop, refresh, run the two queries in the .dax file and compare them with run.py, then test each role with View as.
• Add released and unused lists, so utilisation can be measured against the full timetable as well as the lists that ran.
• Add a second calculation group for per-list averages, to learn how its precedence interacts with the time items.