3. DAX fundamentals for injury metrics

Injury metrics from CAE output are rarely a single number. A typical crashworthiness study produces, for each simulation run, a time history of head acceleration, chest deflection, femur force, and belt force, from which scalar injury criteria such as HIC15, HIC36, chest deflection peak, and Nij are derived. When these results are loaded into Power BI, the analyst must decide, for every derived quantity, whether it should be materialized as a calculated column or evaluated on demand as a measure. That decision is not stylistic. It determines the storage cost of the model, the correctness of aggregations across runs and configurations, and whether slicers and cross-filters behave as expected. This chapter establishes the two evaluation contexts that underpin every DAX expression — row context and filter context — and shows how they map onto the CAE result table.

The shape of a CAE injury results table

Consider a simplified fact table, InjuryResults, with one row per occupant per simulation run:

InjuryResults
RunID | Configuration | Occupant | BodyRegion | Metric   | Value  | Unit | Limit
R-001 | Baseline      | Driver   | Head       | HIC15    | 612.4  | -    | 700
R-001 | Baseline      | Driver   | Chest      | Defl_max | 34.2   | mm   | 42
R-001 | Baseline      | Passenger| Head       | HIC15    | 588.1  | -    | 700
R-002 | Alt_A         | Driver   | Head       | HIC15    | 731.8  | -    | 700
R-002 | Alt_A         | Driver   | Chest      | Defl_max | 38.9   | mm   | 42

Each row is already a scalar summary of a time history; the time-series data lives elsewhere (or is pre-processed upstream). The analytical questions are: what is the worst HIC15 across all runs, how many runs exceed the limit, and what is the margin per configuration? Each question implies a different evaluation context.

Row context: computing per-row derived values

A row context exists when DAX evaluates an expression for a single row of a table. Calculated columns always evaluate in row context: the engine iterates the table, and for each row the column references resolve to that row's values. If we want a per-row margin, we add a calculated column:

Margin = InjuryResults[Limit] - InjuryResults[Value]

LimitExceeded = IF ( InjuryResults[Value] > InjuryResults[Limit], 1, 0 )

Both columns are computed once at refresh, stored in the VertiPaq model, and compressed alongside the rest of the table. For a table with a few thousand rows this is negligible; for a table with millions of time-step rows it is not. The critical property of a calculated column is that it is row-scoped: it cannot see the whole table, only the current row, unless you explicitly lift the row context with an iterator such as SUMX or MAXX.

This is where analysts new to DAX most often go wrong. Writing SUM ( InjuryResults[Value] ) inside a calculated column does not produce a per-row value; it produces the grand total of the entire column, repeated on every row, because the aggregation ignores the row context. To compute a per-run total inside a calculated column you must use an iterator:

RunTotalValue =
SUMX (
    FILTER ( InjuryResults, InjuryResults[RunID] = EARLIER ( InjuryResults[RunID] ) ),
    InjuryResults[Value]
)

EARLIER captures the outer row's RunID so the inner FILTER can restrict the table to the same run. This works, but it is expensive and, more importantly, it is usually the wrong tool: totals across runs are exactly what measures are for.

Filter context: aggregating across the model

A filter context is the set of filters applied to the model at the moment a measure is evaluated — slicers, visual axes, page filters, and cross-filter selections from other visuals. Measures have no row context of their own; they are evaluated once per cell of a visual, inside whatever filter context that cell defines. A measure is never stored; it is computed at query time.

Worst HIC15 =
CALCULATE (
    MAX ( InjuryResults[Value] ),
    InjuryResults[Metric] = "HIC15"
)

Runs Exceeding Limit =
COUNTROWS (
    FILTER (
        InjuryResults,
        InjuryResults[Value] > InjuryResults[Limit]
    )
)

Margin to Limit =
MIN ( InjuryResults[Limit] ) - MAX ( InjuryResults[Value] )

Placed in a matrix with Configuration on rows and Occupant on columns, each cell evaluates Worst HIC15 under the filter context defined by that configuration–occupant intersection. Change the slicer from Baseline to Alt_A and every cell recomputes. No column is stored, and the measure automatically respects any future slicing dimension — test facility, sled configuration, dummy type — without modification.

A useful rule of thumb: if a value must be sliced, filtered, or aggregated differently in different visuals, it belongs in a measure. If it is a fixed attribute of a row that you want to sort, group, or filter by, it belongs in a calculated column.

How the two contexts interact

The interaction becomes concrete when a measure needs a row-level value. CALCULATE transitions row context into filter context: inside an iterator, CALCULATE can take the current row's values and turn them into filters on the model. This is the mechanism behind context transition, and it is why SUMX ( InjuryResults, [SomeMeasure] ) behaves differently from SUMX ( InjuryResults, InjuryResults[Value] ) — the first evaluates the measure once per row with that row's values as filters, the second simply sums a column.

For injury metrics, a common and correct pattern is to keep the raw scalar results in the fact table, add only genuinely row-level attributes as calculated columns (for example, a Status column classifying each result as Pass, Marginal, or Fail against its limit), and express every cross-run statistic as a measure. The SVG below maps this division of labour onto the actual objects in the model.

Row Context vs. Filter Context in the InjuryResults Model Calculated Columns — Row Context Evaluated once per row at refresh · stored in model Margin = Limit − Value Row-scoped: sees only the current row Stored, compressed, sortable, groupable Status = IF(Value > Limit, "Fail", "Pass") Fixed attribute of each result row Usable on slicers and visual axes Pitfall: SUM(Value) in a column Returns grand total on every row Fix with SUMX + FILTER + EARLIER Storage cost Fine for run-level rows (thousands) Avoid on time-step rows (millions) Measures — Filter Context Evaluated per visual cell at query time · never stored Worst HIC15 = CALCULATE(MAX(Value), Metric="HIC15") Respects slicers, axes, cross-filters Recomputes for every configuration cell Runs Exceeding Limit = COUNTROWS(FILTER(...)) Counts rows where Value > Limit Aggregates across the filtered set Margin to Limit = MIN(Limit) − MAX(Value) Worst-case margin per filter context No stored column required Context transition CALCULATE turns row context into filter context Enables per-row measure evaluation in iterators CALCULATE transition

Applying the distinction to a concrete question

Suppose the safety team asks: "For each configuration, how many runs exceed the HIC15 limit, and what is the worst margin?" The correct implementation keeps InjuryResults as a narrow fact table and defines two measures. Runs Exceeding Limit uses FILTER to test each row against its own limit, then COUNTROWS to count survivors; because it is a measure, the count is automatically scoped to whatever configuration the visual displays. Margin to Limit uses MIN(Limit) − MAX(Value) so that the reported margin is the worst case within the current filter context, not an average that could mask a failing run.

If instead the analyst materializes LimitExceeded as a calculated column and then writes SUM(InjuryResults[LimitExceeded]) as a measure, the result is numerically identical for simple cases but the model now carries an extra column, and any change to the limit definition requires a full refresh rather than a measure edit. The calculated column is justified only when the flag must appear on a slicer or be used to sort or group rows — for example, filtering a table visual to show only failing results.

Do not place time-series rows (one row per millisecond per channel) in the same table as run-level injury scalars. Mixing granularities forces either enormous calculated columns or measures that must repeatedly filter by Metric and RunID. Keep the time history in a separate table related on RunID, and compute HIC or peak values upstream or in a dedicated measure that respects the relationship.

Verification and common failure symptoms

After building the measures, verify them against a known subset. Filter the report to Configuration = "Alt_A" and Occupant = "Driver"; the matrix should show Worst HIC15 = 731.8 and Runs Exceeding Limit = 1 for the sample data above. If the measure returns the grand total across all configurations instead, the filter is being ignored — usually because the measure references a column from an unrelated table or because ALL has been applied inadvertently. If a calculated column returns the same value on every row, the expression contains a bare aggregation where a row-level reference was intended. If totals look correct but subtotals do not, check whether the measure uses SUM over a column that already contains a total, which double-counts.

The discipline is straightforward: use calculated columns for stable, row-level attributes that must be sliced or grouped; use measures for every statistic that must respond to filter context. In injury-metric reporting, almost every number the safety team cares about — worst HIC, count of failures, margin to limit, pass rate by configuration — is a measure. The calculated columns are the small set of descriptive flags and categories that make those measures easy to slice.

← Previous 📖 Contents Next →