Testing and validation Track

In BI, a number is wrong until it's proven right. This track turns "looks fine" into tests you can run again after every change: on the data, on the measures, on security, on refresh and on performance. The last topic is a lab where you deliberately break a model, watch what happens and learn to recognise the symptom later.

Before you start Power BI Desktop with DAX query view, the course datasets and starter project, the company pack, and data/tracks/testing/mini_sales.csv (eight rows whose answers you can work out by hand). DAX Studio is optional.

Data quality tests

Data: NWOrders NWOrderLines NWReturns NWFx RawOrdersExport

Why it matters: Every report inherits the quality of its data. Tests that run on every load catch duplicates, orphans and impossible values before an executive does.
Typical production failure: A re-run export duplicates 37 lines; nobody notices for a quarter because the totals "looked about right".
When to use it: Uniqueness of keys, non-null required columns, referential integrity, valid ranges, row-count and total reconciliation with the source, and freshness. Run them on every load; fail loudly.
When not to: Testing only in Power Query by eye. Tests that aren't automated stop being run.

Assignments

A data quality suite for the company pack

B · Objective 45 min · uses orders, order_lines, returns, fx_rates

Write one query (or one script) that runs a set of named data quality tests and returns, for each, the number of failing rows. It must run after every load and be easy to read.

Requirements

Uniqueness of (OrderID, LineNo) in order_lines.
Referential integrity: lines and returns point to existing orders.
Valid ranges: quantity > 0; returned quantity not above the sold quantity; ship date not before order date.
Every EUR order month has an exchange rate.
Flag test customers.

Expected result: Failures: duplicate line keys 37, orphan lines 0, orphan returns 0, non-positive quantities 0, over-returns 0, ship before order 0, missing FX months 0, test-customer orders 3.

Reconcile row counts and totals, source to model

B · Objective 30 min · uses RawOrdersExport (course dataset)

For the Beginner clean-up of RawOrdersExport, write the reconciliation that proves where every source row went: kept, removed as header or total, or removed as a duplicate.

Requirements

Row counts at each step.
A check that kept + removed = source.
Where you would put this so it runs on every refresh.

Expected result: 30 source rows = 27 kept + 1 report header + 1 TOTAL row + 1 duplicate. A Power Query step or a validation query that compares the counts on every refresh.

Which tests should block a release?

D · Ambiguous 20 min · uses —

Elena asks you to classify data tests into "block the load", "warn and continue" and "report weekly". There is no single right answer: justify yours.

Work out

Duplicate keys in a fact.
A dimension member missing for some facts.
A 15% drop in daily row count.
Nulls in an optional column.
A refresh finishing 30 minutes later than usual.

What good looks like: A defensible split, e.g. block: duplicate fact keys; warn: missing dimension members (route to Unknown) and a large row-count drop; weekly: nulls in optional columns and refresh duration trends, with reasons tied to business impact.

Interview questions

Which data quality tests would you always run on a fact table?
Where should data quality tests run?
What's the difference between a failed test and a known defect?
Why reconcile counts, not just totals?

Assessment

A fact table's key isn't unique. The safest treatment is:

Ignore it
Fail the load and investigate
Average the duplicates
Remove the key column

Sales lines reference a product that isn't in DimProduct. Best practice:

Drop those lines
Map them to an Unknown product member and report the count
Delete the product dimension
Use a bi-directional relationship

Add a Data Quality page to your Sprint 01 report: one card per test with its failing-row count, red when above zero.

Measures like COUNTROWS(FILTER(VALUES(FactSales[LineKey]), CALCULATE(COUNTROWS(FactSales)) > 1)) for duplicates; keep it on a hidden or admin page.

Semantic model tests: known input, known output

Data: MiniSales

Why it matters: Measures are code. A small table whose answers you can work out by hand turns "the total looks plausible" into a test that fails the moment a measure changes behaviour.
Typical production failure: Someone "fixes" time intelligence and YoY silently changes for every month that crosses a year boundary; nobody notices until the annual review.
When to use it: A mini dataset with hand-computed answers, DAX queries (EVALUATE) that return measure values in specific filter contexts, and a stored expected result to compare against after every change.
When not to: Testing only on production data, where you don't know the right answer.

Assignments

Work the answers out by hand first

A · Guided 30 min · uses mini_sales

Open mini_sales.csv: eight lines. Value = Qty × UnitPrice. L8 is a return (negative quantity).
On paper, compute: total sales; Q1 2025; Q1 2026 by order date; Q1 2026 by ship date; YoY for Q1; distinct customers in 2026 and in March 2026; Online sales in Q1 2026.
Load the file into a new Power BI file with a date table: an active relationship on OrderDate, an inactive one on ShipDate.
Write the measures: Sales, Sales (ship date) with USERELATIONSHIP, Sales PY, YoY %, Customers.
Compare every measure with your paper answers.

Expected result: Total 1,800; Q1 2025 350; Q1 2026 1,450 by order date and 1,400 by ship date; YoY 314.3%; 4 customers in 2026, 3 in March 2026; Online Q1 2026 600.

Turn them into a DAX test query

B · Objective 30 min · uses mini_sales

Write one DAX query that returns each test's name, expected value, actual value and pass/fail, so anyone can rerun the whole suite in seconds after a change.

Requirements

One row per test, including the inactive-relationship and year-boundary cases.
Expected values typed in from your paper answers, not copied from the measures.
A pass flag that tolerates rounding.

Expected result: Every row shows Pass. Change Sales PY to use DATEADD(…, -12, MONTH) incorrectly on purpose and at least the YoY test fails.

What should a measure return here?

C · Problem 25 min · uses mini_sales

Total rows and edge cases are where measures break. Decide what each measure should return in four awkward contexts, test it, and fix what's wrong.

Work out

YoY % for a month with no sales last year.
Customers at the grand total (is it the sum of the months?).
Sales for a product filtered to a date range with only a return.
Sales (ship date) for March 2026, when one March order ships in April.

What good looks like: YoY blank (not infinity or 0) without prior-year sales; customers at the total = distinct across the period, not the sum of months; the return-only context shows a negative number; ship-date March excludes the line shipped in April.

Interview questions

What is a known-input, known-output test for a measure?
Which contexts most often break measures?
How do you run model tests automatically?
Why type expected values in instead of computing them with another measure?

Assessment

Your DAX test says YoY for January 2026 = blank. January 2025 had no sales. Is that right?

No, it should be 0%
Yes: there is no prior value to compare with
No, it should be 100%
It should be infinity

The distinct customer count for Q1 is lower than the sum of the three months. This means:

A bug
Some customers bought in more than one month
The date table is wrong
Customers were deleted

Store your DAX test query and its expected output in the repository next to the PBIP, and write a one-paragraph README on how to run it.

Later, the automation track shows how to run it against the Service with the executeQueries REST API.

Security tests

Data: UserRegionMapping FactSales DimRegion

Why it matters: Security that isn't tested is a hope. An RLS test matrix with expected numbers per user is the only evidence Compliance can rely on, and it catches regressions when someone changes a relationship.
Typical production failure: A relationship is made inactive to fix an ambiguity; RLS silently stops reaching one fact table (Sprint 03).
When to use it: A matrix of users × roles × expected visible data, run with View as (or DAX queries executed as the user) on every release, including tools that bypass the report.
When not to: Checking that the report "looks filtered". Page filters and hidden visuals aren't security.

Assignments

An RLS test matrix with expected numbers

B · Objective 40 min · uses UserRegionMapping, FactSales, DimRegion

Using the dynamic RLS from Intermediate (UserRegionMapping with USERPRINCIPALNAME), write and run a test matrix that would satisfy Compliance.

Requirements

Every user in the mapping table plus one user who isn't in it.
Expected net sales per user, computed independently (SQL or Excel), not from the report.
Actual result with View as, and pass/fail.

Expected result: Dana $12,164.13, Marcus $3,634.46, Nina (West + East) $17,863.94, Grace (All) $33,391.13; the unmapped user sees nothing. 11 users and 12 mapping rows in total.

Break the security on purpose

C · Problem 30 min · uses starter project

Make three changes that a well-meaning colleague might make, rerun your matrix after each, and record which ones it catches: make a relationship bi-directional, make the Region relationship inactive, and remove a user's row from the mapping table.

Work out

Record the matrix result after each change.
Add a test that would have caught anything your matrix missed.

What good looks like: Inactive relationship: every regional user sees all regions (caught). Removed mapping row: that user sees nothing (caught only if every user is in the matrix). Bi-directional relationship: filters can leak into other tables; caught only if the matrix also checks a dimension list or another fact.

Test what the report hides

B · Objective 20 min · uses starter project

Show that page filters and hidden visuals aren't security, and add tests that use the tools a user with Build permission has.

Requirements

Test with Analyze in Excel or a new report on the model, not just the published report.
Test object-level security (a hidden Salary column) separately from RLS.

Expected result: The page filter disappears in Analyze in Excel; RLS still applies; OLS makes the column not exist for the restricted role (visuals using it error).

Interview questions

How do you test RLS?
What happens when a user is in two roles?
Does RLS apply to workspace Admins, Members and Contributors?
Why aren't page filters security?

Assessment

A report author is a workspace Member. With RLS on the model, they see:

Only their region
All data: RLS doesn't apply to Members
Nothing
Only aggregated data

A user is in the West role and the East role. They see:

West only
East only
West and East
Neither

Write the security matrix for Sprint 03 (payroll) as a reusable template your team can apply to any model.

Columns: user, role(s), should see, must not see, sensitive objects visible?, test query, expected, actual, pass. See Templates → RLS / OLS test matrix.

Refresh tests, performance budgets and regression

Data: FactSales DimDate

Why it matters: Most production incidents aren't wrong DAX; they're refreshes that succeeded with the wrong data, pages that got slower one measure at a time, or a fix that broke something else.
Typical production failure: A refresh reports Completed with a warning for weeks while one table is stale (Sprint 07).
When to use it: Post-refresh checks (rows, max date, partitions), a written performance budget measured the same way every release, and a regression baseline of key numbers compared before and after each change.
When not to: Treating "refresh succeeded" as "data is right".

Assignments

Post-refresh checks

B · Objective 30 min · uses FactSales, DimDate (course)

Write the checks that should run after every refresh of the course model, so a stale or partial load is caught before users see it.

Requirements

Row count of FactSales against the source file.
Max OrderDate against the expected data date.
No blank keys after load; every FactSales date exists in the date table.
For incremental refresh: the partitions you expect exist (DAX INFO functions or XMLA).

Deliver

A DAX query returning check name, expected, actual, pass.

Expected result: FactSales has 100 rows, max OrderDate 2026-03-31, no blank keys; the course DimDate fails the date coverage check (one ship date in April), which is exactly why Beginner replaces it.

Write and enforce a performance budget

C · Problem 30 min · uses starter project

Elena wants a performance budget for the executive page so Sprint 04 never happens again. Define it, measure it the same way every time, and say what happens when a change breaks it.

Work out

Budgets for cold page load, slicer interaction, refresh duration and model size.
The measurement method (cache, machine, tool).
What blocks a release and what only warns.

What good looks like: For example: page < 3 s cold, slicer < 1 s, refresh < 30 min, model < 1 GB, measured with Performance Analyzer after clearing the cache on the test machine; a breach of page or slicer budget blocks the release, others warn.

Prove you didn't break anything else

B · Objective 30 min · uses starter project

Before changing the Net Sales definition (Sprint 02's ledger change), capture a regression baseline, make the change, and prove that only the intended numbers moved.

Requirements

A baseline: a DAX query returning ten key measures by month, saved to a file.
The same query after the change; a comparison listing every difference.
An explanation for each difference (expected or not).

Expected result: Only the measures depending on Net Sales change, by exactly the amounts in the change's impact assessment; Gross Sales, quantities and counts are identical.

Interview questions

Refresh succeeded but the numbers are wrong. What do you check?
What is a performance budget?
What is a regression test in BI?
How do you check incremental refresh is working?

Assessment

A refresh history shows Completed with a warning that a table wasn't refreshed. You should:

Ignore it; it completed
Treat it as a failure for production models and alert the owner
Restart the gateway
Delete the table

Which measurement is comparable across releases?

Whatever the developer saw on their laptop
Performance Analyzer after clearing the cache, same machine and dataset
User feelings
Refresh time on a busy capacity

Write your team's release checklist section "Tests" (data, model, security, performance, regression) in five lines.

See Templates → Deployment checklist and Validation.

Build it, break it: the failure lab

Data: FactSales DimCustomer DimRegion DimProduct

Why it matters: You recognise a failure quickly only if you've seen it before. Breaking a model on purpose, in a safe copy, builds that recognition without waiting for a production incident.
Typical production failure: A team spends two days on a "random" wrong total that anyone who had seen bi-directional ambiguity would have spotted in minutes.
When to use it: For each failure: build the working version, break it deliberately, observe the symptom, diagnose it with tools, fix it, explain it in two sentences, and write the check that prevents it.
When not to: Doing this on a shared or production model. Always work on a copy.

Assignments

Break the relationships

B · Objective 45 min · uses starter project

In a copy of the starter project, make the relationships misbehave in two ways and learn the symptoms.

Requirements

Build: Net Sales by RegionName from DimRegion and by the customer's region, as in Beginner.
Break 1: set DimCustomer ↔ FactSales to bi-directional and add DimCustomer[RegionKey] → DimRegion. Observe what Power BI does and what your visuals show.
Break 2: make FactSales → DimRegion inactive. Observe the totals.
For each: the symptom, how you diagnosed it, the fix, a two-sentence explanation, and the check that would have caught it.

Expected result: Break 1: Power BI refuses or deactivates a relationship because of an ambiguous path (or results depend on which path wins). Break 2: every region shows the grand total. Checks: no bi-directional relationships without an ADR; a test that regional totals sum to the grand total and differ from each other.

Break the performance

B · Objective 45 min · uses enterprise pack (1M rows) or starter project

Generate a larger fact (Resources → Enterprise scale pack) and make it slow on purpose, three ways, measuring each with Performance Analyzer and DAX Studio.

Requirements

An iterator that evaluates a measure per row of the fact (e.g. SUMX(FactSales, [Net Sales]) or FILTER over the fact with a measure inside).
A calculated column on the fact with a high-cardinality result (e.g. a timestamp as text).
A query that stops folding (a custom function per row before a filter).

Expected result: The iterator shows many storage-engine queries or callbacks and FE time; the calculated column inflates model size (VertiPaq Analyzer) and refresh; the broken folding shows a greyed-out View Native Query and a longer refresh. Each fix restores the baseline.

Break the refresh

C · Problem 30 min · uses starter project

Simulate the two most common refresh failures and write the runbook entry for each.

Work out

Schema drift: rename a column in a source CSV that the model uses.
Incremental refresh misconfigured: RangeStart/RangeEnd filter on a column that doesn't fold, or with >= on both ends.
For each: the error or symptom, the diagnosis, the fix, the prevention.

What good looks like: Schema drift: "The column … wasn't found" naming the table; fix the step or the source; prevent with a data contract. Incremental: duplicated or missing rows at partition boundaries (>= on both ends) or a full scan every refresh (no folding); fix with >= RangeStart and < RangeEnd on a foldable column.

Interview questions

Why break things on purpose?
What symptom suggests an ambiguous relationship path?
Why does >= RangeStart AND <= RangeEnd break incremental refresh?
What is schema drift and how do you defend against it?

Assessment

Making FactSales → DimRegion inactive, with no other change, makes a matrix by RegionName show:

Correct values
The grand total on every row
Blanks
An error

View Native Query is greyed out on a Power Query step against SQL Server. This means:

The query is fast
Folding stopped at or before this step
The step has an error
The source is a file

Write a one-page "failure field guide": for each failure from this lab, symptom → likely cause → first diagnostic step.

This is the start of your team's runbook (Templates → Runbook).