The Dyson Sphere Series | Part 1: Dynamic Measures

The Pain: Measure Explosion

Measure explosion is a real bottleneck when building a Power BI report, and the current approaches to solve it are inadequate.

​

Example (5 Metric Report)

Metrics: Net Sales, Cost, Margin, Order Count, Margin per Order

Calculations needed: Current Year, Prior Year, YoY, YoY%

Result: - SUM (5) + Prior Year (5) + YoY (5) + YoY% (5) = 20 measures to write and maintain

​

Current Approaches

User Defined Functions (UDFs): Solve authoring speed. They don't solve measure explosion, model bloat, or format string management.

Calculation Groups: Come close, but fall short on format string handling. A calc item can only interrogate the base measure via SELECTEDMEASURENAME() - it cannot interpret itself. As a result, all calc items must be hardcoded and iterated on every single calc item.

​

Solution

There's a better way. And I'm going to show you how and why it works.

One measure. One dispatcher. Every metric, every derived calculation, every visual - all routed through a single engine that responds to a slicer selection automatically.

That's Dynamic Measures. 

​

Scope

Limitation: Dynamic Measures is not built for multi-metric side-by-side layouts. If you need Net Sales, Margin, and Order Count as separate columns in the same table, this isn't the right tool for that.

Strength: One metric, full depth. Switch the slicer and every attribute column CY, PY, CY/GT%, YoY(±YoY%) updates across the entire hierarchy simultaneously. The visual below runs entirely off one [Calc] (CY in the table) measure.

Prognosis

Every base metric you add doesn't cost you 1 measure. It costs you 4.

Base Metrics | Traditional  | Dynamic    | Reduction
-------------|--------------|------------|----------
5            | 20 measures  | 8 measures | 60%
15           | 60 measures  | 18 measures| 70%

The traditional approach requires a SUM, a Prior Year, a YoY, and a YoY% for every single base metric - each one written individually, each one maintained individually. The model doesn't scale, it accumulates.

And the cost compounds. At 5 metrics you're writing 20 measures. At 15 you're writing 60. Every new metric request from a stakeholder is a silent tax on your model. Six months from now you'll be afraid to touch anything because you've lost track of what depends on what.

Diagnosis

A single [Calc] measure reads a slicer selection and routes to the corresponding base metric using SWITCH(SELECTEDVALUE()).

[PY], [YoY], and [YoY%] all reference [Calc], not the individual base measures. Switch the slicer - everything updates. Add a new base metric - add one line to the SWITCH. Nothing else changes.

Your base SUMs stay; you still need one per metric. But Prior Year, YoY, and YoY% collapse into one measure each, regardless of how many base metrics you have. Three measures do the work of however many your report demands.

Treatment

Step 1 - Create the Field Parameter Table

Create a table named Metric in your model with the following data:

Base       | Sort
-----------|-----
NetSls$    | 1
Cost$      | 2
Margin$    | 3
OrderCT    | 4
Mgn$PerOrd | 5

The Base column is what the slicer reads and what the dispatcher references. Keep the values consistent with your fact table column names - it reduces confusion when wiring up the SWITCH later. The Sort column controls display order in the slicer.

​

Step 2 - Create the Base Measures

One SUM measure per metric. These are the foundation everything else builds on.

Adopt a naming convention: prefix each base measure with an underscore. It keeps them visually separated from your derived measures in the field list and signals to anyone reading the model that these are inputs, not outputs.

_NetSls$  = SUM(Transactions[NetSls$])
_Cost$    = SUM(Transactions[Cost$])
_Margin$  = SUM(Transactions[Margin$])
_OrderCT  = SUM(Transactions[OrderCT])

One important exception - ratio metrics.

If a metric is a ratio between two other metrics, don't create a column for it and SUM it. Sum of ratios produces incorrect totals. Instead, derive it by dividing the two SUM measures:

_Mgn$PerOrd = DIVIDE([_Margin$], [_OrderCT], 0)

This ensures the total row reflects the true weighted ratio - Margin total divided by OrderCT total - rather than a meaningless sum of per-row calculations.

​

Step 3 - Create the Calc Dispatcher

This is the engine. SWITCH(SELECTEDVALUE()) reads the active slicer selection from Metric[Base] and routes to the corresponding base measure.

Calc =
SWITCH(
    SELECTEDVALUE(Metric[Base], "NetSls$"),
    "Cost$",      [_Cost$],
    "Margin$",    [_Margin$],
    "OrderCT",    [_OrderCT],
    "Mgn$PerOrd", [_Mgn$PerOrd],
    "NetSls$",    [_NetSls$],
                  [_NetSls$]  -- fallback: unmatched selection defaults to Net Sales
)

Two things worth noting:

The default value in SELECTEDVALUE(Metric[Base], "NetSls$") ensures visuals never open blank. The report always shows something meaningful on load.

The final line is a fallback for any unmatched selection. If a Metric[Base] value doesn't match any SWITCH case it defaults to Net Sales rather than returning blank. This is a safety net, not expected behavior - keep your Metric[Base] values and SWITCH keys in sync.

​

Step 4 - Create the Supporting Measures

With [Calc] in place, the three supporting measures almost write themselves. Each one references [Calc] directly - switch the slicer, all three update automatically.

-- Prior Year: lifts the date filter then shifts context back one year
PY =
CALCULATE(
    [Calc],
    REMOVEFILTERS(Transactions[Date]),
    SAMEPERIODLASTYEAR(Transactions[Date])
)

-- YoY: returns BLANK instead of zero when either side has no data
YoY =
IF(
    NOT(ISBLANK([Calc]) || ISBLANK([PY])),
    [Calc] - [PY]
)

-- YoY%: ABS([PY]) prevents sign flip on negative prior year values
YoY% = DIVIDE([YoY], ABS([PY]))

Remission

That's it. 6 measures regardless of how many base metrics your report carries. Swap the slicer, everything follows.

The pattern scales cleanly. Add a new base metric, add one line to the SWITCH, done. No touching PY, YoY, or YoY%. No rebuilding. No maintenance spiral.

Below includes both a PBIX template file and a DAX code file. Drop in your own base measures and wire up your own metrics. The dispatcher is ready to go.

GitHub: Download Template + DAX

Connect on LinkedIn: linkedin.com/in/mkienchau