Why a Measure Is Wrong and Nobody Noticed

Why do broken measures survive?

Because they return a number, and a number looks like an answer.

A pipeline that fails is loud. It leaves a red run, a missing partition, an alert in a channel someone reads. A measure that is wrong is silent by construction: the DAX evaluates, the visual renders, the card fills. Nothing in the stack knows that the value is not the thing the label claims it is. The only detector is a human who already knows the right answer, and that human is usually not looking at the dashboard — they are looking at their own spreadsheet, which is how the problem started.

So correctness in reporting is not a runtime property you can catch with monitoring. It is a design property you have to assert up front and re-assert on a schedule.

What actually goes wrong?

The failure classes are boring and repeat everywhere.

Grain mismatch. A fact table at order-line grain joined to something at order grain, or a dimension that turns out not to be unique on the key you joined it with. The sum inflates by exactly the fan-out factor, which is stable enough to look like real growth.

Non-additive maths treated as additive. Averages of averages. Ratios summed across a hierarchy. Balances and headcounts summed across time when they should be taken at a period end. Each is correct at one level of a hierarchy and wrong at every other, so the measure passes whatever check was done on the page it was built for.

Distinct counts. A distinct count does not decompose. The parts will not sum to the total, and that is correct behaviour — but if nobody wrote that down, the first person to notice files it as a bug, and the second person fixes it into something that is genuinely wrong.

Date role confusion. Order date, ship date, invoice date, effective date, load date. A model with one active relationship silently picks one of them for every measure in the file. Nothing in the visual tells the reader which.

Unmatched rows. Facts whose dimension key does not resolve land in a blank member. Put any filter on that dimension and they leave the total. The report gets quieter and quieter as referential integrity degrades, and quieter reads as “business is soft”.

Filter context that has been cleared. Measures written with functions that remove filters do exactly what they were asked to. They also bypass the filters you did not intend them to bypass.

Refresh boundaries. Incremental refresh processes only changed partitions rather than a full dataset. That is the point of it — and it means a correction applied upstream to a period whose partition is no longer in the refresh window never lands. The DAX is right. The data behind it is stale in a specific, invisible slice.

Why doesn’t anyone report it?

Because the cost of raising it falls on the person who raises it.

A figure inside the plausible band gets absorbed. The reader has a rough prior, the number matches the prior, and they move on. When a figure does not match, the mismatch is usually resolved in conversation rather than in the model: someone offers a reason, the reason is accepted, and the reason is never written down or tested. Repeat that a few times and the report acquires a body of oral tradition about which numbers to trust on which pages.

The other reason is that reconciliation tends to be a launch activity. The model is checked against a source of truth once, signed off, and never checked again — while the upstream schema, the join keys and the business rules all keep moving. Correctness was verified for a state of the world that no longer exists.

What makes a measure testable?

Declaring the things that are currently implicit. A measure is testable when you can state, without opening it: its grain, which date role it uses, whether it is additive, and what it should reconcile to.

Most of these checks are properties, not values, which is what makes them durable. Totals equal the sum of their parts, unless the measure is declared non-additive. The count of fact rows after joining dimensions equals the count before. A period measure recomputed over the union of its partitions equals the same measure computed whole. None of those assertions needs a target figure, so none of them goes stale.

Where do the tests live?

Split them. Structural properties belong upstream, in the transformation layer, where they run on every build: uniqueness on the declared grain key, referential integrity between fact and dimension so nothing drifts into the blank member, accepted values on the columns your filters depend on. Reconciliation belongs at the boundary between the mart and whatever the organisation treats as authoritative.

with mart as (
  select order_date_key as date_key,
         sum(net_amount) as amt
  from {{ ref('fct_order_line') }}
  group by 1
),

src as (
  select order_date_key as date_key,
         sum(net_amount) as amt
  from {{ ref('stg_orders__lines') }}
  group by 1
)

select
  coalesce(mart.date_key, src.date_key) as date_key,
  mart.amt as mart_amt,
  src.amt  as src_amt
from mart
full outer join src
  on mart.date_key = src.date_key
where coalesce(mart.amt, 0) <> coalesce(src.amt, 0)

Inside the semantic model, keep a reconciliation page that nobody outside the team opens: control totals by period, the delta against the source aggregate, row counts by dimension including the unmatched bucket, and the same figures rendered once per security role. It is unglamorous and it is the only surface where a silent break becomes visible before a reader finds it.

When is a measure safe to publish?

What does the review habit look like?

Dashboards decay in predictable ways: pages accumulate, definitions fork, filters get left on, a column is renamed upstream and a visual quietly loses a category, an owner changes roles and the report keeps running with nobody accountable for it.

A standing review with a fixed agenda handles most of it. Walk the usage telemetry and find what nobody opens. Find measures with near-duplicate names — that is where a fork happened. Read the reconciliation deltas since the last review rather than only the current value. Re-run the role matrix. Then do the part everyone skips: retire something. A report that is not retired is a report someone will cite in three years, and the older it is, the more authority it carries and the less anyone remembers how it was defined.

Who owns the definition?

A semantic layer centralises metric definitions so separate reports agree. That is the mechanism, but the discipline is contractual: names, grain, filter semantics and a change process that deprecates rather than edits in place. When a definition genuinely has to change, publish the new measure alongside the old one, mark the old one deprecated, and let readers see both move together for a period before the old one is removed. The alternative — silently editing the expression behind a name people already trust — is the single most reliable way to make every historical figure in every deck unverifiable.

The test of a reporting layer is not whether it was right on the day it shipped. It is whether, when it goes wrong, the model tells you before a reader does.