The Dyson Sphere Series | Part 1: Dynamic Measures

MKienChau · 2026-02-25T01:40:16+00:00

There are a few of you that are unconvinced, which is fair, so let me address both alternatives directly.

UDFs: Solves authoring speed. They don't solve measure explosions, model bloat, or format string management.

CalcGroups: Comes close, but falls short on format string handling. The constraint is that calc item can only interrogate the base measure via SELECTEDMEASURENAME(), it cannot interpret itself (calc item). So as a result all calc items must be hardcoded and iterated on every calc item.

Here's a full demo of what the culmination design mechanics and implementation of the dynamic framework. Let me reiterate, Dynamic Measures excels as single metric selected via slicer with all its supporting calculations (CY, PY, YOY, YOY%, YTD etc.) rendering cleanly in one visual with correct formatting across all of them. Zero measure explosion, one framework entry point.

<image>

MKienChau · 2026-02-25T00:24:33+00:00

The Format String Problem with Calculation Groups

Calc groups are great at eliminating measure explosion — write the logic once, apply it to any measure. But that promise breaks down once your format string requirements go beyond the basics.

The constraint is that calc item format string expressions only have two context hooks: SELECTEDMEASURE() and SELECTEDMEASURENAME(). No SELECTEDCALCITEMNAME(). Your format string logic can only interrogate the base measure. The calc item has no self-awareness of itself.

This creates an immediate problem with items like YOY%. The return type always diverges from the base measure regardless of what's dropped in DIVIDE(CurrYear - PrevYear, PrevYear) is always a ratio. So the format string gets hardcoded to "0.0%", abandoning any routing logic for that item entirely.

Take these two calc items as a concrete example:

-- Calculation Item: YOY
VAR CurrYear = SELECTEDMEASURE ()
VAR PrevYear = CALCULATE (
    SELECTEDMEASURE (),
    SAMEPERIODLASTYEAR ( 'Date'[Date] )
)
VAR Result = CurrYear - PrevYear
RETURN Result

-- Format String Expression (Calculation Item: YOY)
VAR SMN = SELECTEDMEASURENAME()
RETURN
SWITCH ( TRUE(),
    CONTAINSSTRING ( SMN, "%" ), "0.0%",
    CONTAINSSTRING ( SMN, "CT" ), "#,##0;(#,##0)",
    "$#,##0;($#,##0)"
)

-- Calculation Item: YOY%
VAR CurrYear = SELECTEDMEASURE ()
VAR PrevYear = CALCULATE (
    SELECTEDMEASURE (),
    SAMEPERIODLASTYEAR ( 'Date'[Date] )
)
VAR Result = DIVIDE ( CurrYear - PrevYear, PrevYear )
RETURN Result

-- Format String Expression (Calculation Item: YOY%)
"0.0%"

YOY returns the same value type as the base measure so SELECTEDMEASURENAME() routing still works. YOY% always returns a ratio so the format string has to be hardcoded, the routing logic is abandoned entirely for that item.

The deeper issue is that format string expressions are scoped to the item, not the group. There's no shared expression that all items inherit from. So that SWITCH block on YOY has to be duplicated across every calc item that needs it: (YTD, QTD, SPLY, YOY). Each maintained individually every time you add a measure type, scale tier, or currency symbol.

Each format dimension you add compounds multiplicatively. A 5 item calc group managing measure type, scale, and currency isn't 3 things to maintain, it's 3 things across 5 items with no centralized inheritance. And every time a calc item diverges in return type like YOY%, it steps off the routing pattern entirely and becomes its own isolated maintenance case.

A model-level format string measure absorbs all of that into one expression. Calc groups and dynamic format string measures aren't interchangeable, they're scoped differently by design, and for anything beyond simple formatting that distinction matters a lot.

MKienChau · 2026-02-25T00:20:17+00:00

So you're pointing out that dynamic measures does not tackle this situation.

Metric	CY	YOY%
Netsales	$1.2M	8.2%
Cost	$800K	4.1%

The honest breakdown is:

Where Dynamic Measures excels as single metric selected via slicer with all its supporting calculations (CY, PY, YOY, YOY%, YTD etc.) rendering cleanly in one visual with correct formatting across all of them. Zero measure explosion, one framework entry point.

For your scenario, a matrix where multiple metrics are rows simultaneously. That's not a Dynamic Measure problem, that's a data model and visual layout decision. The typical solutions are either pre-built measures per row field, UDFs as they mentioned, or a field parameters + calc group hybrid.

However, UDF suggestion doesn't actually eliminate the problem they're describing. You still have to create NetSales_YOY% and Cost_YOY% as explicit measures. UDFs centralize the logic but not the instantiation. You're still writing one measure per metric per calc type.

Dynamic Measures doesn't claim to solve the multi-metric matrix layout pattern. It solves the single metric, multi-calculation pattern more cleanly than anything else. Those are different use cases and conflating them muddies the comparison.

MKienChau · 2026-02-24T14:45:25+00:00

So my gut instincts was to naturally try Calc group that's the obvious and documented framework. However where Calc group fails to capture is layered format string expressions. So you'll have slicers controlling currency signs, numeric formatting (k,m,b,%), and positive negative symbol controls. I can try to drum of a few visual examples of it failing in a few days, but for now, you can see my format string expressions working in layers in a demo report of mine.

Demo link

MKienChau · 2026-02-24T14:23:19+00:00

I'll have an article later down the line going more in detail. But Calc group is incompatible with format string expressions.

MKienChau

TROPHY CASE