4. DAX basics for crash metrics

Crash safety dashboards live or die by the correctness of a small set of numbers: how many crashes occurred, how severe the injuries were, and whether the trend is improving year over year. In Power BI, those numbers are produced by DAX, and the single most consequential design decision is whether a given calculation should be a calculated column or a measure. Getting this wrong does not usually produce an error message — it produces plausible-looking numbers that are wrong at the total level, which is far more dangerous in a safety context.

The rule of thumb is structural: a calculated column is evaluated row by row at refresh time and stored in the model, so it belongs in a table and can be used as a slicer, axis, or filter field. A measure is evaluated at query time inside the filter context of whatever visual calls it, so it belongs nowhere in particular and always responds to slicers, page filters, and row/column groupings. Crash counts, severity percentages, and year-over-year deltas are all measures, because they must aggregate correctly across whatever slice the user selects. Calculated columns are reserved for row-level attributes that do not yet exist in the source — a severity band, a crash-year key, a boolean flag for "injury crash."

Model assumptions for the examples

The examples below assume a star schema typical of crash databases: a fact table Crashes with one row per crash, containing CrashID, CrashDate, SeverityCode (K, A, B, C, O — Killed, Incapacitating, Non-incapacitating, Possible injury, Property damage only), Fatalities, Injuries, and a Date dimension marked as a date table with a Year column. All figures shown are illustrative sample values, not measured results.

Calculated columns: severity band and crash year

Severity codes are terse and sort badly alphabetically (A before K). A calculated column that maps the code to a numeric rank and a readable label makes slicing and sorting reliable:

Severity Rank =
SWITCH (
    Crashes[SeverityCode],
    "K", 1,
    "A", 2,
    "B", 3,
    "C", 4,
    "O", 5,
    BLANK ()
)

Severity Label =
SWITCH (
    Crashes[SeverityCode],
    "K", "Fatal",
    "A", "Incapacitating",
    "B", "Non-incapacitating",
    "C", "Possible injury",
    "O", "Property damage only",
    "Unknown"
)

Crash Year = YEAR ( Crashes[CrashDate] )

These columns are computed once per refresh and stored, so they cost memory but almost nothing at query time. Note the deliberate BLANK() for an unrecognized code in Severity Rank: it surfaces data-quality gaps instead of silently bucketing bad codes into a valid severity. After adding the column, set Sort by column on Severity Label to Severity Rank so charts order K → O rather than alphabetically.

Measures: crash counts and severity distribution

The base count is trivial but should still be written as an explicit measure rather than dropped onto a visual as an implicit aggregation, so it can be reused and formatted consistently:

Crash Count = COUNTROWS ( Crashes )

Injury Crashes =
CALCULATE (
    [Crash Count],
    Crashes[SeverityCode] IN { "K", "A", "B", "C" }
)

Fatal Crashes =
CALCULATE ( [Crash Count], Crashes[SeverityCode] = "K" )

Severity distribution is where filter-context discipline matters. The share of each severity within the current selection must divide by the count with the severity filter removed, otherwise every row shows 100%:

Severity Share =
VAR CurrentSeverity = [Crash Count]
VAR AllSeverities =
    CALCULATE ( [Crash Count], REMOVEFILTERS ( Crashes[SeverityCode] ) )
RETURN
    DIVIDE ( CurrentSeverity, AllSeverities )

REMOVEFILTERS strips only the severity filter, so a slicer on Date[Year] = 2023 still applies and the shares sum to 100% within that year. Using ALL(Crashes) instead would also remove date filters and produce shares against the entire database — a classic and hard-to-spot error. Format the measure as a percentage and verify that the severity shares in a matrix sum to exactly 100.0%.

Year-over-year comparison

Year-over-year requires shifting the filter context back one year on the date table, not on the fact table:

Crash Count PY =
CALCULATE ( [Crash Count], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

Crash Count YoY =
VAR Current = [Crash Count]
VAR Prior = [Crash Count PY]
RETURN
    IF ( NOT ISBLANK ( Prior ), Current - Prior )

Crash Count YoY % =
VAR Current = [Crash Count]
VAR Prior = [Crash Count PY]
RETURN
    IF ( NOT ISBLANK ( Prior ), DIVIDE ( Current - Prior, Prior ) )

The ISBLANK guard prevents a misleading −100% or a division error in the first year of the dataset, where no prior period exists. Because SAMEPERIODLASTYEAR depends on a contiguous, marked date table, confirm the Date table has no gaps and is marked as a date table before trusting these measures.

Verification and common failure symptoms

Before publishing, run three checks. First, drop [Crash Count] into a card and compare it with a direct row count of the Crashes table in the source query — they must match exactly. Second, build a matrix of Severity Label by Date[Year] with [Severity Share] and confirm each year's column totals 100%. Third, place [Crash Count YoY %] on a line chart and confirm the earliest year is blank rather than −100%.

Typical symptoms map to specific causes. If severity shares exceed 100% or sum inconsistently, a filter other than SeverityCode is being removed — check for ALL(Crashes) or ALLSELECTED misuse. If [Crash Count PY] returns blanks for every year, the Date table is not marked or the relationship to Crashes is inactive. If totals in a table disagree with the sum of visible rows, a calculated column was used where a measure was needed, or an implicit aggregation was placed on the visual. If Severity Label sorts alphabetically, the Sort by column setting was not applied. Each of these is diagnosable from the visual itself, which is why the verification checks belong in the dashboard build, not in a later review.