Testing and Reliability of Numbers

This theme measures how much trust can be placed in a number and the evidence behind it. It covers reconciliation with the source, measure testing through DAX queries (DAX Studio…

Guide≈ 7 min readCheckedReading

In brief

This theme measures how much trust can be placed in a number and the evidence behind it. It covers reconciliation with the source, measure testing through DAX queries (DAX Studio, SSMS, query execution API), static analysis (Best Practice Analyzer in Tabular Editor), non-regression testing, reference datasets, and business acceptance testing. General code tests are in DL — Testing; upstream data quality is in PD — data quality.

On this page

1. Why this theme
2. What this theme measures
3. The 5 levels
4. Milestones by level
5. Detailed reading by level
6. Partial achievement and rating
7. Moving to the next level
8. See also

Level: 🟢→🔴 · Theme code: TST

1. Why this theme#

A wrong report destroys more value than no report at all: trust is lost in one meeting and takes six months to rebuild. BI errors are silent: a changed relationship, a forgotten filter, a source grain change produce plausible but wrong totals. Unlike application code, there is no exception or crash: only a test can say “this number is wrong.” A model without tests is a model people are afraid of.

2. What this theme measures#

Axis: from occasional visual checks to automated non-regression that blocks delivery.
Key indicators: share of measures covered by a test; share of deliveries preceded by a full test suite; number of numeric discrepancies found by users rather than by the team; detection time.
Units: %, number of discrepancies/quarter, hours.

3. The 5 levels#

Level	Short name	Promise	Typical indicator (orders of magnitude)
1	Quick glance	The number was reviewed by its author on the day of delivery.	0 retained tests; acceptance testing = screenshot
2	Documented checks	Each major delivery has a written, signed set of manual checks.	Completed checklist; discrepancies tracked
3	Replayable queries	A check can be rerun identically in a few minutes by any team member.	≥ 50% of key measures covered by a query
4	In pipeline	A regression on a key number stops delivery before users see it.	≥ 80% of key measures automatically tested; BPA in pipeline
5	Guaranteed	Discrepancies are detected in production before users, and each incident adds a test.	0 discrepancies found by a user over 6 months

4. Milestones by level#

ID	Milestone	Verifiable evidence
BI-TST-1.1	The author compares at least one total with the source	Check record
BI-TST-1.2	A business user reviewed the report before publication	Review note
BI-TST-1.3	Known discrepancies are acknowledged in writing	List of known discrepancies
BI-TST-2.1	Acceptance testing checklist per report (totals, filters, boundary periods)	Completed checklist
BI-TST-2.2	Control data has a source of truth (SQL query, reference extract)	Retained reference query
BI-TST-2.3	Explicit tolerated discrepancies (rounding, time zone offsets, closing)	Written threshold
BI-TST-2.4	Signed business acceptance testing before production go-live	Signed validation
BI-TST-3.1	DAX test queries retained in repository (one per key measure)	Versioned `.dax` files
BI-TST-3.2	Expected values come from independent SQL, not from the measure itself	Cross-check comparison
BI-TST-3.3	Stable reference dataset (frozen set or snapshot)	Documented dataset
BI-TST-3.4	Edge cases tested: empty period, single filter, total, start/end of year, null values	Cases in the test set
BI-TST-3.5	Best Practice Analyzer run regularly on the model	BPA report
BI-TST-4.1	Tests run automatically (script or pipeline)	Execution log
BI-TST-4.2	A failing test blocks promotion (IND)	Blocking policy
BI-TST-4.3	Role-based security tests (see RLS) included	Results by role
BI-TST-4.4	Reference performance tests included (PRF)	Thresholds
BI-TST-4.5	Schema tests (expected columns) before refresh (ETL)	Schema alert
BI-TST-5.1	Planned reconciliation checks in production (source ↔ model)	Check dashboard
BI-TST-5.2	Each incident leads to a non-regression test	Incident ↔ test link
BI-TST-5.3	Statistical alert thresholds (unusual discrepancy, trend break)	Tracked alerts
BI-TST-5.4	Coverage measured (measures, edge cases, roles) and published	Coverage indicator

5. Detailed reading by level#

🟢 Level 1 — Quick glance#

Signs: “looks good,” no retained tests, numbers are not compared to anything.
Difficulty: 1/5 · Effort: 1/5.

🟢 Level 2 — Documented checks#

Signs: written checklist: total by month = SQL total; “France” filter = sum of regional filters; a month with no data does not crash. Discrepancies are documented and signed off by the business.
Gain: first audit trail; shared acceptance testing.
Limit: slow, manual, rarely repeated in later changes.
Difficulty: 2/5 · Effort: 2/5.

🟡 Level 3 — Replayable queries#

A measure test is a DAX query with an independent expected value:

// tests/Nb_Visites_Realisees.dax
DEFINE
    VAR vAttendu = 12345      // issu d'une requête SQL de référence, jeu figé 2026-09
EVALUATE
ROW (
    "Mesure",  [Nb Visites Réalisées],
    "Attendu", vAttendu,
    "OK",      [Nb Visites Réalisées] = vAttendu
)

A second test verifies internal consistency: sum by region = total; monthly total = yearly total.

EVALUATE
VAR vSomme = SUMX ( VALUES ( 'DIM_COMMERCIAL'[Region] ), [Nb Visites Réalisées] )
RETURN ROW ( "Somme", vSomme, "Total", [Nb Visites Réalisées],
             "OK", vSomme = [Nb Visites Réalisées] )

Signs: queries are in the repository; anyone can run them in DAX Studio, SSMS, or a script. Best Practice Analyzer (Tabular Editor) detects model defects (summarized numeric columns, bidirectional relationships, missing formats, unused columns) without running calculations.
Gain: testing costs minutes, not hours.
Limit: execution is still manual; drift between test and measure is not automatically detected.
Difficulty: 3/5 · Effort: 3/5.

✅ Good practice

The expected value must come from an independent calculation path (SQL on the source, not the same logic copied into DAX). A test that reuses the same logic fails in the same way.

⚠️ Pitfall

Symptom: all tests pass, user sees a discrepancy. Cause: tests run without filters, on a total; the discrepancy appears only with a segment filter or under an RLS role. Fix: test frequent intersections (period × region) and each role.

🟡 Level 4 — In pipeline#

Signs: the pipeline (IND) loads the test model, runs queries (query execution API, XMLA, or command-line tool), compares, and publishes the result. A failure blocks promotion. BPA runs with configured rules (a few “blocking” rules, others as warnings).
Gain: regression does not slip through silently.
Limit: vague tests (threshold too lax) or unstable tests (variable dataset).
Difficulty: 4/5 · Effort: 4/5.

🧪 Example

SFA common thread: after a column rename on the datamart side, a schema test fails before deployment; without it, the measure [Nb Visites Réalisées] would have returned 0 with no error (column not found handled as BLANK in the visual).

💡 Expert tip

Distinguish three families of tests: accuracy (expected value), consistency (invariants: sum of parts = total, N-1 of N = entered N-1), behavior (empty period, excluded filter, role). Invariants are the most robust to dataset changes.

🔴 Level 5 — Guaranteed#

Signs: planned reconciliation checks in production; alerts on statistical breaks (a region drops to zero); each incident adds a test; coverage (measures, edge cases, roles) is published.
Gain: the team learns from its mistakes systematically.
Limit: tests to maintain; false positives; maintenance budget.
Difficulty: 5/5 · Effort: 5/5.

🤖 AI

An assistant generates a DAX test skeleton per measure and suggests edge cases people forget. It cannot provide the expected value: that comes from the business or from SQL. Risk: auto-generated tests that rerun the measure and always “pass.”

6. Partial achievement and rating#

Common rule. Sample-based measurement: among 10 key measures, how many have a test query, an independent expected value, and automatic triggering? “Partial 5”: production reconciliations exist for the 5 main facts (BI-TST-5.1 = 0.5), the incident ↔ test link has been maintained for 4 months (5.2 = 1), no statistical alerts (5.3 = 0), coverage tracked internally but not published (5.4 = 0.5).

7. Moving to the next level#

Possible means (optional):

Transition	Possible levers
1 → 2	Acceptance testing checklist; reference SQL queries; discrepancy thresholds
2 → 3	`.dax` files in repository; frozen dataset; Tabular Editor/BPA
3 → 4	Test runner (PowerShell/Python script); promotion blocking; schema and role tests
4 → 5	Planned reconciliation; statistical alerts; incident review → test

Pitfalls:

False positive: 90% coverage of tests that rerun the measure logic.
Goodhart’s law: targeting a coverage rate → trivial tests (COUNTROWS > 0). Evaluate the tests’ ability to fail when something is intentionally broken (manual mutation testing).
Testing only the happy path: defects live at the edges (first day, last day, null values, key duplicates).
Non-representative test dataset: unrealistic cardinalities and volumes hide performance problems.

8. See also#

Related themes: DAX · MOD · IND · RLS · OPS
Other matrices: DL — Testing · PD — Data Quality · PD — UAT and Business Validation · PI — Quality and Acceptance (QUA)
Content: Power BI Reporting Project Checklists · Cross-Cutting Power BI Best Practices · CI/CD for Data: Databases, Datamarts, Pipelines, and Reports · Version Control for Data: SQL and Power BI

✻ Written by Claude Sonnet 5.5