Calculation groups in practice

A model with 17 measures that needs prior year, change on prior year, percentage change, financial year to date and a rolling twelve months has two options. Write 85 more measures, each a copy of the same five patterns, or write the five patterns once as a calculation group. This article covers how calculation groups work, the functions and properties that matter, and where they go wrong.

The examples come from my theatre utilisation semantic model, whose Time Intelligence calculation group I loaded with Microsoft’s TMDL serializer. I haven’t opened that model in Power BI Desktop, so the model’s numbers quoted here come from a pandas version of the same logic, not from Power BI. Time intelligence for NHS financial years and rolling periods covers the date logic inside the items; this article is about the calculation group around them.

What a calculation item does

A calculation group shows up in a report as a table with one column, and each value in it is a calculation item: a DAX expression with SELECTEDMEASURE() standing in for whatever measure is being evaluated. When a visual filters the column to one item, DAX replaces each measure reference in the query with the item’s expression, with the original measure in place of SELECTEDMEASURE().

So with the PY item selected, [Touch-time Utilisation] is evaluated as:

CALCULATE ( [Touch-time Utilisation], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

Three consequences follow:

• Only explicit measures. An implicit measure (a column dragged into a visual and summed) is generated as inline DAX with no measure reference to replace. That’s why a model needs discourage implicit measures turned on before Power BI Desktop will create a calculation group; Desktop asks the first time. Existing implicit measures in visuals keep working; you just can’t add new ones.
• One item at a time. The group is applied only when a single item is in the filter context. Select two items in a slicer and, by default, the measures come back unchanged.
• No measure reference, no effect. As SQLBI puts it, a calculation item replaces measure references, so CALCULATE ( SUMX ( Sales, ... ), 'Time Intelligence'[Period] = "PY" ) does nothing: there is no measure to replace.

The four functions

• SELECTEDMEASURE() is the placeholder for the measure being evaluated. It can only be used in a calculation item or a format string expression.
• SELECTEDMEASURENAME() returns that measure’s name as text. Microsoft says it’s often used for debugging, and a comparison against the name as text isn’t updated when the measure is renamed.
• ISSELECTEDMEASURE ( [M1], [M2], ... ) returns TRUE if the measure is one of those listed. The references are real measure references, so renaming a measure updates the list. Use it, not name strings, to exclude measures.
• SELECTEDMEASUREFORMATSTRING() returns the measure’s format string. It’s meant for format string expressions, where it lets an item build on the measure’s own format.

A calculation group in TMDL

In TMDL a calculation group is a table with a calculationGroup block, the items inside it, and two columns: the item name (sourceColumn: Name) and an ordinal for sorting. The start and end of the theatre model’s group:

/// Time comparisons for any measure. Put Period on a visual, a slicer or a filter; with no selection, measures are unchanged.
table 'Time Intelligence'

	calculationGroup
		precedence: 10

		/// The measure as it is.
		calculationItem Current = SELECTEDMEASURE ()

		/// The same dates a year earlier. Blank where those dates are before the data starts.
		calculationItem PY = CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

	column Period
		dataType: string
		summarizeBy: none
		sourceColumn: Name
		sortByColumn: Ordinal

	column Ordinal
		dataType: int64
		isHidden
		formatString: 0
		summarizeBy: sum
		sourceColumn: Ordinal

Two things I found by loading it with the Tabular Object Model’s TmdlSerializer. The order of the calculationItem blocks sets the ordinal: when I added ordinal: lines, the serializer read them and dropped them when it wrote the model back, and its output reloaded with the same order. And the file needs no partition; the loaded model gets one anyway. Without an ordinal, items would sort alphabetically, and nobody wants FYTD between Current and PY.

Dynamic format strings

An item with no format string expression keeps the measure’s format. That’s right for PY and FYTD, wrong for a percentage change of a count. Each item can have a format string expression, a DAX expression that returns the format string to use.

The theatre model’s PY Change builds on whatever format the measure has, adding a sign:

		/// 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

So [Early-finish Minutes] with format #,0 shows +2,354, and [Touch-time Utilisation] with 0.0% shows +0.5%. For a rate that difference is in percentage points, and the item’s description says so.

PY Change % needs a fixed percentage format, and for rates it shouldn’t return anything, because a percentage change of a percentage is a number people misread:

		/// Relative change on PY. Blank for the rates, where a percentage of a percentage misleads.
		calculationItem 'PY Change %' =
				IF (
					NOT ISSELECTEDMEASURE (
						[Touch-time Utilisation],
						[Session Utilisation],
						[Late-start Rate],
						[On-the-day Cancellation Rate]
					),
					VAR CurrentValue = SELECTEDMEASURE ()
					VAR PriorValue =
						CALCULATE ( SELECTEDMEASURE (), SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
					RETURN
						IF ( NOT ISBLANK ( CurrentValue ), DIVIDE ( CurrentValue - PriorValue, PriorValue ) )
				)

			formatStringDefinition = "+0.0%;-0.0%;0.0%"

A measure can have its own dynamic format string too. Microsoft’s documentation is clear about who wins: a measure’s dynamic format string counts as lower precedence than any calculation group in the model.

Precedence

With two calculation groups filtered at once, precedence decides how they nest. The highest-precedence item is applied first and ends up outermost, and its SELECTEDMEASURE() is replaced by the next item down, then the next, until the measure is reached. Microsoft’s example: a measure returning 10, a precedence-100 item SELECTEDMEASURE() + 2 and a precedence-200 item SELECTEDMEASURE() * 2 give ((10) + 2) * 2 = 24, not 14.

That matters as soon as one group changes the filter context. Microsoft’s second example has a time intelligence group at precedence 20 and an averages group at 10 with a daily average item. YTD daily average then becomes:

CALCULATE ( DIVIDE ( SELECTEDMEASURE (), COUNTROWS ( DimDate ) ), DATESYTD ( DimDate[Date] ) )

The year-to-date filter wraps both the total and the day count. With the precedences swapped, the day count would come from the current month alone. My rule: time intelligence gets the higher precedence, so it wraps everything else. Only the highest-precedence group’s format string is applied, so check formats too when you add a second group.

Selections: none, one or several

By default an item applies only when exactly one item is selected. Two optional expressions on the group change that. noSelectionExpression runs when the group isn’t filtered at all, and suits a default such as converting to a reporting currency. multipleOrEmptySelectionExpression runs when several items are selected, or an item that doesn’t exist. Each can have its own format string expression.

Without them, the model’s selectionExpressionBehavior decides what a multiple selection returns: automatic (the default, which behaves as nonvisual) returns the measure unchanged, and visual returns BLANK. In a slicer on Period I’d make single-select mandatory rather than rely on either.

Calculation groups and field parameters

A field parameter lets a report reader choose the measure in a visual; a calculation group changes what that measure computes. They combine well, because Power BI resolves the field parameter first. SQLBI’s traces show the query a visual sends names the chosen fields directly, so the calculation item replaces the chosen measure like any other measure reference. In the theatre model a slicer on Theatre Measure picks the measure and a slicer on Period picks the comparison, and SELECTEDMEASUREFORMATSTRING() hands PY Change the right format either way.

Three details to plan around:

• Implicit measures don’t work in either. Microsoft lists them as a field parameter limitation too, so a model built on explicit measures serves both.
• SELECTEDVALUE fails on a field parameter column. Power BI sets a group-by column on the parameter’s display column, which makes it part of a composite key, and SELECTEDVALUE relies on HASONEVALUE, which can’t be used on such a column. SQLBI’s workaround summarises the parameter table by both columns, keeps the display column, and tests COUNTROWS ( … ) = 1 instead.
• Clients differ. Calculation groups work in Excel PivotTables through MDX. Field parameters are metadata that only Power BI reads; SQLBI noted in 2022 that Excel ignores them.

Pitfalls

Measures become variant. Once a model has a calculation group, Power BI reports treat every measure as the variant data type. Microsoft warns this can break a dynamic format string that reuses another measure, and suggests wrapping it in FORMAT ( [Measure], "" ) or moving the logic into a DAX user-defined function.

Arithmetic on text measures. A measure that returns text, such as a dynamic title, errors when an item does arithmetic on it. Microsoft’s fix is to test ISNUMERIC ( SELECTEDMEASURE () ) first.

Applying items to expressions in your own DAX. CALCULATE ( DIVIDE ( [Cost], [Sales] ), 'Time Intelligence'[Period] = "YTD" ) replaces both measure references separately. For YTD the answer happens to be the same; SQLBI shows cases where it isn’t. Their rule: in CALCULATE, apply a calculation item to a single measure, never to an expression. Visuals already do that.

Recursion. Microsoft supports sideways recursion, where an item filters its own group to another item, as a YoY % built from YoY and PY does, and says other types of recursion aren’t supported. I keep items free of measure references other than SELECTEDMEASURE(). My FYTD item needs the last date with data, so it reads MAX ( Sessions[Session Date] ) from the column rather than calling a [Last Data Date] measure.

Snapshot measures. A waiting list or a bed count at a point in time has no meaningful year to date. Exclude such measures with ISSELECTEDMEASURE, or the report will sum a stock over months. Time intelligence for NHS financial years and rolling periods has the pattern.

Security. Row-level and object-level security can’t be defined on a calculation group table, though they work on the other tables. Smart narrative visuals and detail rows expressions aren’t supported with calculation groups.

Test it with a query

A calculation group is DAX like any other, so test it with a query whose answer you know. The theatre model’s download includes this one, which puts every item side by side:

EVALUATE
    SUMMARIZECOLUMNS (
        Site[Site],
        'Time Intelligence'[Ordinal],
        'Time Intelligence'[Period],
        TREATAS ( { "Sep 2025" }, 'Date'[Month] ),
        "Touch-time utilisation", [Touch-time Utilisation]
    )
ORDER BY Site[Site], 'Time Intelligence'[Ordinal]

The project computes the same items in pandas from the same CSVs. For the main hospital in September 2025 they should come back as Current 70.2%, PY 69.7%, PY Change +0.5%, FYTD 71.2% and Rolling 12M 70.1%, with no PY Change % row: the item is blank for a rate, and SUMMARIZECOLUMNS drops a row whose only value is blank. If a number disagrees, either the item or the reference is wrong, and finding out which is the point of the test. I haven’t run this query in Power BI myself, which is exactly why it ships with its expected answer.

Checklist

• Explicit measures only, and discourage implicit measures on.
• The items in the order readers expect, with Current first.
• A format string expression on every item that changes the unit.
• ISSELECTEDMEASURE to exclude rates and snapshots where an item makes no sense.
• Time intelligence at a higher precedence than any per-unit or currency group.
• Guards for dates after the last data and for windows that start before it.
• A DAX query with known answers for every item, run after each change.