Testing and UAT for BI Reports: A Checklist That Finds Errors

Think about how a piece of custom code reaches production. Unit tests, a code review, a QA pass, a staging environment, a change ticket, a sign-off. Now think about how a dashboard reaches production. Someone builds it, someone glances at it in a meeting, and it goes live. BI reports carry as much decision weight as any application, and they get a fraction of the scrutiny. That gap is why one wrong figure on one tile can destroy trust in the entire estate. Nobody says "that one number is off." They say "I do not trust these reports," and then they go back to their own spreadsheet. Trust in analytics is binary and it does not recover cheaply.

This is a checklist, not a theory piece. It defines five test layers, the reconciliation pack that anchors them, the edge cases that break reports in production but never in test, and the regression routine that keeps a report honest after go-live.

Why dashboards ship undertested

The root cause is that a report looks finished long before it is correct. Charts render, numbers appear, colours are on brand, and the visual polish reads as done. There is no compiler error for a measure that silently double-counts, no crash for a filter that excludes reversed transactions. A report can be wrong and beautiful at the same time, and the beauty is what people evaluate in the room.

The second cause is that the person best placed to catch a wrong number, the data owner who knows the business total off by heart, is rarely in the test loop. Testing gets delegated to whoever built the model, and a builder tests that the report matches their own understanding, not that their understanding matches the source system. The five layers below exist to close both gaps.

Layer 1: Source reconciliation

What it catches: the model quietly disagreeing with the system of record. This is the single highest-value test and the one most often skipped. The question is simple. For the same filter, does the total in the model equal the total in the source system? Pick "revenue, FY2025, region North," run it in the model, then run the equivalent query directly against the source, and compare. If the ETL dropped rows, changed a join grain, or filtered out a status you did not know existed, this is where it surfaces. Reconciliation is not a spot check you do once. It is a formal pack, covered in its own section below.

Layer 2: Measure logic

What it catches: a formula that is plausible but wrong. Every non-trivial measure needs at least one hand-calculated test case: a small, known slice of data where you can work out the expected answer on paper, then confirm the measure returns exactly that. Averages of averages, running totals that reset at the wrong grain, percentage measures that divide by the wrong denominator, year-over-year that silently compares against a partial prior period. These do not look wrong, they look reasonable, which is precisely why they survive to production. If you write DAX, the DAX reference is worth checking on evaluation-context behaviour, because most measure bugs are context bugs, not arithmetic bugs. Test every measure that feeds a headline number, not just the complicated-looking ones.

Layer 3: Filter and slicer interaction

What it catches: combinations that produce impossible results. A report tested one slicer at a time can still break when two slicers combine. Select a region and a product line that never overlap and the tile should read zero, not blank, not the grand total, not an error. Cross-filtering between visuals is the usual culprit: clicking a bar in one chart re-scopes another in a way the builder never intended, and a percentage jumps above 100 or a count exceeds the total population. Test the combinations a real user will actually click, especially the ones that should return nothing, because an empty result handled badly is where the ugliest numbers appear.

Layer 4: Security and RLS

What it catches: a role seeing rows it should not, or missing rows it should. Row-level security is where testing failures become incidents rather than embarrassments. For every RLS role, confirm it sees exactly the expected set: not too much, which is a data leak, and not too little, which quietly hides work and erodes trust. Test the boundaries specifically, the user who belongs to two regions, the manager who should see their team but not peers, the account with no mapping at all. I have covered the modelling side of this in row-level security for multi-site reporting; the testing rule is to verify each role against a known expected row count, never against a vague sense that "it filtered something."

Layer 5: Performance under concurrency

What it catches: a report that is fast for one tester and unusable on Monday morning. Single-user performance in a dev workspace tells you almost nothing about behaviour when forty people open the same dashboard at 9am after a scheduled refresh. Test with realistic concurrency and realistic data volume, not a trimmed sample. Watch refresh windows, query timeouts, and the visuals that trigger expensive queries on load. The Power BI documentation covers capacity and refresh limits worth understanding before you promise a service level. A report nobody can load at the moment they need it is functionally as broken as one with wrong numbers.

The reconciliation pack

The core artefact of BI testing is a reconciliation pack: a table of 15 to 25 control totals, each with the source-system value, the model value, the variance, and an agreed tolerance, signed off by the data owner before go-live. It is the document that turns "I think the numbers are right" into "the person who owns these numbers confirmed they are right." Keep it in the project record. When someone challenges a figure six months later, the pack is your evidence.

A non-zero variance is not automatically a failure, but every non-zero variance must have a documented reason inside its tolerance, for example a rounding difference or an agreed timing cut-off. A variance with no explanation is a defect. The full pack for a real estate should run 15 to 25 lines and cover every headline number on the report plus the totals people already know in their heads.

Edge cases that break in production

These are the failures that pass every test in a clean environment and then appear the first week real data flows through. Test them deliberately, because they will not surface on their own.

• Null and blank in denominators. A ratio measure looks fine until a division hits a zero or null denominator and returns infinity, an error, or a misleading blank. Decide what a divide-by-nothing should show and test that it actually shows it.
• First and last period partial data. The current month is incomplete and the earliest month may be a stub from go-live. Trend lines that treat a partial period as complete produce fake cliffs. Confirm partial periods are flagged or excluded on purpose, not by accident.
• Deleted or reversed source records. A cancelled invoice, a reversed journal, a soft-deleted work order. If the model counts the original but not the reversal, totals inflate. Test with a record that was created and then reversed and confirm the net is correct.
• Time-zone boundaries on date filters. A transaction stamped 11pm local can land on the wrong day in UTC, moving it across a month or quarter boundary. Test a record near midnight and confirm it falls in the period a human would expect.

Regression and defect triage

A report is not correct once, it is correct continuously. Every model change, a new measure, a refactored relationship, a source schema tweak, can silently break a number that was fine yesterday. The regression routine is simple: re-run the entire reconciliation pack after every model change, before it is promoted. If all control totals still sit inside tolerance, ship. If any moved, you have a regression to explain before go-live, not after. This takes minutes when the pack is written as repeatable queries, and it is the single habit that keeps long-lived reports trustworthy.

When a defect is found, triage it by impact on the number, not by how hard it is to fix:

• Severity 1, wrong headline number. Any figure a decision-maker acts on is outside tolerance. The report does not go live, or comes down if already live. No exceptions.
• Severity 2, wrong in a drill-down or edge combination. Headline is right, a specific slice is wrong. Fix before the next scheduled release, flag the affected view in the meantime.
• Severity 3, cosmetic or non-numeric. Formatting, labels, sort order. Log it, batch it, do not let it block a correct report from shipping.

The line that matters is the top one. A single Severity 1 in production is what people remember, and it taxes every other report you have ever built. Testing usage and trust are linked, which is why I track whether reports actually get opened in measuring BI adoption with usage data, and why the numbers you test should map to the metrics that matter in your KPI tree.

Independence note: I have no commercial relationship with Microsoft or any BI vendor. Power BI and DAX appear here because they are what I work in most often; the five layers, the reconciliation pack, and the triage rule apply to any BI platform.