3. DAX fundamentals for injury metrics

Injury metrics rarely arrive as a single clean number. A crash pulse produces a time series of acceleration; a CAE solver exports nodal displacements, element stresses, and head-center-of-gravity kinematics; a test lab delivers filtered channels sampled at 10 kHz. Before any of that becomes a KPI such as HIC15, BrIC, or a chest deflection pass/fail rate, it has to be shaped into a tabular model that DAX can evaluate correctly. The single most common source of wrong injury KPIs in Power BI is not a bad formula — it is a formula evaluated in the wrong context. This chapter builds the vocabulary needed to avoid that: the distinction between calculated columns and measures, and the row and filter contexts in which each is evaluated.

Calculated columns: materialized per row, computed at refresh

A calculated column is evaluated once per row of its table during model processing, and the result is stored in the VertiPaq column store. Because it is materialized, it can be used as a slicer field, a row label in a matrix, or a relationship key. Its evaluation context is the row context: inside the expression, bare column references resolve to the current row.

Consider a channel-level table AccelerationSamples with columns RunID, Channel, Time_s, and Accel_g. A calculated column that converts to SI units and flags the resultant magnitude is a natural fit:

Accel_ms2 = AccelerationSamples[Accel_g] * 9.80665

SampleInterval_s =
VAR CurrentTime = AccelerationSamples[Time_s]
VAR PreviousTime =
    CALCULATE (
        MAX ( AccelerationSamples[Time_s] ),
        ALLEXCEPT ( AccelerationSamples, AccelerationSamples[RunID], AccelerationSamples[Channel] ),
        AccelerationSamples[Time_s] < CurrentTime
    )
RETURN
    IF ( ISBLANK ( PreviousTime ), BLANK (), CurrentTime - PreviousTime )

The second column is instructive. Row context alone cannot see "the previous sample" — a row context has no notion of ordering. The CALCULATE call transitions the row context into a filter context, restricting the table to earlier timestamps within the same run and channel, and MAX then returns the preceding time. This is context transition in its simplest form, and it is the mechanism behind nearly every time-series derived column in injury analytics.

Measures: evaluated at query time, never stored

A measure has no row context of its own. It is evaluated when a visual requests it, inside whatever filter context the visual, slicers, and cross-filtering have established. That makes measures the correct vehicle for aggregations and for any KPI whose denominator must respond to the user's selection.

Peak Resultant Accel (g) =
MAXX (
    VALUES ( AccelerationSamples[RunID] ),
    CALCULATE ( MAX ( AccelerationSamples[Accel_g] ) )
)

HIC15 =
VAR Window_s = 0.015
VAR Peak =
    MAXX (
        VALUES ( AccelerationSamples[Time_s] ),
        VAR t0 = AccelerationSamples[Time_s]
        VAR WindowSamples =
            FILTER (
                ALL ( AccelerationSamples[Time_s] ),
                AccelerationSamples[Time_s] >= t0
                    && AccelerationSamples[Time_s] <= t0 + Window_s
            )
        VAR MeanAccel =
            AVERAGEX ( WindowSamples, CALCULATE ( AVERAGE ( AccelerationSamples[Accel_g] ) ) )
        RETURN MeanAccel ^ 2.5 * Window_s
    )
RETURN Peak * 1000

Note what the measure does not do: it does not assume a fixed run. If a report page filters to RunID = "FMVSS208-07", the measure computes HIC15 for that run. If the page shows all runs by test configuration, it computes HIC15 per configuration because VALUES ( AccelerationSamples[RunID] ) is itself filtered by the visual's context. The same formula serves both layouts — that is the payoff of measures over columns.

Row context versus filter context, and the transition between them

The two contexts are not interchangeable, and confusing them produces silently wrong numbers rather than errors. A row context exists only inside an iterator (SUMX, MAXX, AVERAGEX, FILTER) or inside a calculated column. A filter context exists in every measure evaluation and is built from slicers, rows and columns of the visual, and explicit CALCULATE modifiers. CALCULATE is the only function that performs context transition: it takes the current row context and converts each row into an equivalent filter.

A frequent injury-metrics bug illustrates the point. Suppose InjuryResults holds one row per run with HIC15, BrIC, and Limit. A calculated column written as InjuryResults[HIC15] / MAX ( InjuryResults[Limit] ) returns the ratio against the global maximum limit, not the row's own limit, because MAX ignores the row context. The corrected column uses context transition explicitly:

HIC Margin =
DIVIDE (
    InjuryResults[HIC15],
    CALCULATE ( MAX ( InjuryResults[Limit] ) )
)

Here CALCULATE converts the current row into a filter on the row's key, so MAX sees only that run's limit. The same pattern applies to any per-run normalization: chest deflection against the 63 mm criterion, neck Nij against the 1.0 limit, or femur force against the 10 kN threshold.

Choosing between the two, and the cost of getting it wrong

The decision rule is straightforward once the evaluation model is clear. If the value is a property of a single row and must be sliced, grouped, or joined on, use a calculated column. If the value is an aggregation whose result must change with the user's selection, use a measure. Injury KPIs almost always belong in the second category: HIC15, BrIC, Nij, and VC are aggregations over a time window, and their pass/fail status is a comparison against a limit that may itself be filtered by regulation or body region.

Two failure modes recur in practice. The first is a measure that references a column without an aggregator, which DAX rejects at authoring time — a visible error. The second is a calculated column that uses an aggregator without CALCULATE, which DAX accepts and which returns a plausible but globally-scoped number — an invisible error. The second is far more dangerous in a CAE performance dashboard, because a margin column that silently divides every run by the same limit will still produce a clean-looking table. Always validate a new margin or ratio column by filtering to a single run and confirming the denominator equals that run's own limit.

A minimal validation checklist

Before publishing any injury-metrics model, verify the following on a single known run. Filter the report to one RunID with a known reference HIC15 from the solver post-processor. Confirm the measure returns that value within rounding. Then remove the filter and confirm the measure recomputes per run rather than repeating the single-run value. For each calculated column that contains an aggregator, temporarily add a card visual showing the column's maximum and confirm it varies across runs. If it does not, context transition is missing. Finally, check the model size impact of calculated columns on the largest fact table; if the column is only needed for a single KPI and not for slicing, convert it to a measure and delete the column.

With these three concepts — materialized row-context columns, query-time filter-context measures, and CALCULATE as the bridge between them — the remaining injury KPIs become formula-writing rather than context-debugging. The next layer of complexity is time intelligence over non-calendar axes, which is where the sample-interval logic introduced above becomes essential.