Test Power BI Measures With Known-Answer DAX Queries

A successful refresh only proves that data loaded. To prove that your measures are right, build a tiny model where you already know every answer, write one DAX query that compares each measure with its expected value under specific filters, and keep that query in the file. Rerun it after every measure or model change, and it will catch mistakes before your stakeholders do.

This guide builds a five-order fixture, writes thirteen tests in DAX query view, and uses them to catch a real filter bug that looks fine on an unfiltered card. The expected values were calculated independently with SQL from the same rows. The DAX uses standard functions documented by Microsoft, but it was not executed in Power BI Desktop for this guide, so run the tests yourself before relying on them; a failing test on a correct model means the test needs fixing, not the model. The fixture is fictional.

Separate logic tests from data checks

Two different questions hide behind "are the numbers right?":

• Is the measure logic right? Test it on a small fixture where you can calculate every answer by hand. If a test fails, the measure or the model is wrong, because the data cannot be.
• Is the real data complete and consistent? Check row counts, orphan keys, and totals against the source system. If one of these fails, the data or the load is wrong.

Mixing the two is why manual click-through testing is slow: when a production total looks odd, you cannot tell whether DAX, relationships, or yesterday's load caused it. This guide does both, but keeps them separate.

Build a fixture with known answers

In a new Power BI Desktop file, use Home > Enter data to create two tables. Name the first Customer:

CustomerID,Region
C1,West
C2,West
C3,East
C4,East

Name the second Sales:

OrderID,CustomerID,OrderDate,Status,Amount
1001,C1,2026-01-05,Complete,120
1002,C1,2026-01-20,Complete,80
1003,C2,2026-01-22,Cancelled,200
1004,C3,2026-02-03,Complete,150
1005,C2,2026-02-10,Complete,60

Set OrderID to Whole number, OrderDate to Date, Amount to Fixed decimal number, and the other columns to Text. In Model view, make sure there is exactly one relationship: Customer[CustomerID] to Sales[CustomerID], one-to-many, single direction. Power BI may detect it automatically; check it rather than assuming.

The fixture is small but deliberate. It contains a cancelled order, a customer with no orders (C4), two months, and two regions. Each row exists to make at least one test meaningful. A real model would also have a date table; this fixture filters Sales[OrderDate] directly to stay short.

Create the measures, including a common bug

Create these measures on the Sales table. The first two contain a mistake that is common in real reports, so leave it in for now:

Revenue = CALCULATE ( SUM ( Sales[Amount] ), Sales[Status] <> "Cancelled" )

Orders = CALCULATE ( COUNTROWS ( Sales ), Sales[Status] <> "Cancelled" )

Average Order Value = DIVIDE ( [Revenue], [Orders] )

Customers With Orders =
CALCULATE (
    DISTINCTCOUNT ( Sales[CustomerID] ),
    KEEPFILTERS ( Sales[Status] <> "Cancelled" )
)

On a card with no slicers, Revenue shows 410, which is correct. The problem only appears under a filter on Status, so a quick visual check passes.

Write the expected answers as a DAX query

Before writing tests, write down what each answer should be from the rows alone: four non-cancelled orders totaling 120 + 80 + 150 + 60 = 410, of which West is 260 and East is 150, January is 200 and February is 210. Nothing about the expected values comes from Power BI.

Open DAX query view, create a query tab, and paste this query:

DEFINE
    VAR Tolerance = 0.005
    VAR Tests =
        UNION (
            ROW ( "Test", "Revenue, all orders", "Actual", [Revenue], "Expected", 410 ),
            ROW ( "Test", "Orders, all orders", "Actual", [Orders], "Expected", 4 ),
            ROW ( "Test", "Average order value, all orders", "Actual", [Average Order Value], "Expected", 102.5 ),
            ROW ( "Test", "Revenue, West", "Actual", CALCULATE ( [Revenue], Customer[Region] = "West" ), "Expected", 260 ),
            ROW ( "Test", "Revenue, East", "Actual", CALCULATE ( [Revenue], Customer[Region] = "East" ), "Expected", 150 ),
            ROW ( "Test", "Revenue, January 2026", "Actual",
                CALCULATE ( [Revenue], Sales[OrderDate] >= DATE ( 2026, 1, 1 ), Sales[OrderDate] < DATE ( 2026, 2, 1 ) ), "Expected", 200 ),
            ROW ( "Test", "Revenue, February 2026", "Actual",
                CALCULATE ( [Revenue], Sales[OrderDate] >= DATE ( 2026, 2, 1 ), Sales[OrderDate] < DATE ( 2026, 3, 1 ) ), "Expected", 210 ),
            ROW ( "Test", "Customers with orders, West", "Actual",
                CALCULATE ( [Customers With Orders], Customer[Region] = "West" ), "Expected", 2 ),
            ROW ( "Test", "Revenue, customer with no orders", "Actual",
                CALCULATE ( [Revenue], Customer[CustomerID] = "C4" ), "Expected", 0 ),
            ROW ( "Test", "Revenue, Status = Cancelled", "Actual",
                CALCULATE ( [Revenue], Sales[Status] = "Cancelled" ), "Expected", 0 ),
            ROW ( "Test", "Orders, Status = Cancelled", "Actual",
                CALCULATE ( [Orders], Sales[Status] = "Cancelled" ), "Expected", 0 ),
            ROW ( "Test", "Region revenue minus total revenue", "Actual",
                CALCULATE ( [Revenue], Customer[Region] = "West" )
                    + CALCULATE ( [Revenue], Customer[Region] = "East" ) - [Revenue], "Expected", 0 ),
            ROW ( "Test", "Sales rows without a customer region", "Actual",
                COUNTROWS ( FILTER ( Sales, ISBLANK ( RELATED ( Customer[Region] ) ) ) ), "Expected", 0 )
        )
    VAR Checked =
        ADDCOLUMNS (
            Tests,
            "Result", IF ( ABS ( COALESCE ( [Actual], 0 ) - [Expected] ) <= Tolerance, "PASS", "FAIL" )
        )

EVALUATE
    Checked
    ORDER BY [Result], [Test]

EVALUATE
    ROW ( "Failed tests", COALESCE ( COUNTROWS ( FILTER ( Checked, [Result] = "FAIL" ) ), 0 ) )

How it works:

• Each ROW is one test: a name, the measure evaluated under one specific filter, and the expected value. CALCULATE supplies the filter a slicer or visual would supply.
• UNION stacks the rows by position and takes column names from the first row, so every ROW must list its three values in the same order.
• COALESCE ( [Actual], 0 ) treats a blank result as 0. That is right for these totals, where "no sales" should display as blank. If blank and zero mean different things in your model, write a separate test with ISBLANK.
• The tolerance avoids false failures from decimal rounding. Keep it far smaller than any difference that matters to the business.
• The query has two EVALUATE statements. Use the Result dropdown in the results grid to switch between the full list and the failure count.

Run it and read the failures

With the measures as written, two tests should fail:

The other eleven pass. The failures describe a real report bug: if a reader selects Cancelled in a Status slicer, the revenue card shows 410, the revenue of the orders that were not cancelled.

The cause is documented CALCULATE behavior. A filter argument such as Sales[Status] <> "Cancelled" replaces any existing filter on the same column. The slicer's Status = "Cancelled" is discarded, and the measure applies its own filter instead. KEEPFILTERS changes that, so the measure's filter is intersected with the existing one. Microsoft's KEEPFILTERS reference shows the same effect with states and provinces.

Fix the two measures:

Revenue =
CALCULATE ( SUM ( Sales[Amount] ), KEEPFILTERS ( Sales[Status] <> "Cancelled" ) )

Orders =
CALCULATE ( COUNTROWS ( Sales ), KEEPFILTERS ( Sales[Status] <> "Cancelled" ) )

Rerun the query. All thirteen tests should pass, and the second result should show Failed tests as 0. Under a Cancelled filter, the intersection of "Cancelled" and "not Cancelled" is empty, so Revenue is blank.

You can also edit measures inside DAX query view. Right-click a measure in the Data pane and choose Quick queries > Define and evaluate; after you change the definition and run the query, the Update model CodeLens writes it back to the model. See Microsoft's DAX query view documentation.

Prove the tests can fail

A test suite that never fails proves nothing. Break the data on purpose: open Transform data, edit the Sales table's Source step, and add this sixth row, whose customer does not exist in Customer:

1006,C9,2026-02-14,Complete,40

Apply the change and rerun the test query. Six tests should now fail:

The four total tests say that something changed. The last two say why: 40 of revenue belongs to no region, because one sales row has no matching customer. Power BI assigns such rows to a blank member of Customer, so region visuals quietly show a blank row or a total that is larger than its visible parts. Remove the extra row to restore the fixture.

Keep the tests with the model

DAX query view saves query tabs in the Power BI Desktop file, so the tests stay with the model. Rename the tab to something like Tests: revenue measures. If you save the report as a Power BI project (PBIP), each query tab is written as a .dax file in the semantic model's DAXQueries folder, where it can be reviewed and versioned like any other file. Saved queries are not shown to viewers in the Power BI service.

Use a simple rule: no measure change is published until the test query shows zero failures. When you add a measure, add at least one test that would fail if it ignored a filter, a relationship, or a blank. When someone reports a wrong number, reproduce it as a failing test first, then fix the measure until the test passes. The test then guards against the same bug returning.

Keep the fixture in a separate development file with the same table and column names as production, or load fixture tables into a development copy of the model. The tests reference model objects by name, so they move with the measures when you copy them.

Check the real data separately

Logic tests on a fixture do not prove that production data is complete. For the production model, use checks that do not need hand-calculated answers:

• Integrity checks that should always be 0, such as fact rows without a matching dimension row and region revenue minus total revenue. The last two tests above work unchanged on real data.
• Reconciliation checks against the source at a stated grain, such as revenue by month from a SQL query using the same status rule and date boundaries. The metric reconciliation playbook explains how to agree that rule first, and testing SQL with expected results covers the SQL side.
• Totals that should not add up, such as ratios and distinct counts, need their own expected values rather than a sum of the rows. See Power BI totals that do not add up.

Run these after each refresh that matters, before a release, and whenever a source system changes. If row-level security is involved, test the reader's access separately; the dynamic RLS testing guide explains why the author's view is not enough.

FAQ

Can I run these tests in the Power BI service?

You can open DAX query view on a published semantic model with Write DAX queries if you have write permission and the workspace allows editing data models in the service. Queries written there are discarded when you close it, and queries saved in Desktop are not shown, so keep the source of truth for tests in the Desktop file or the PBIP project.

How many tests does a measure need?

Enough to cover each way it can be wrong: one unfiltered total, one test per filter or relationship it should respect, one for empty results, and one for any special rule such as excluded statuses. Thirteen tests for four measures is typical; a complex measure may need more.

Why not compare the report against last month's export?

A previous export tells you whether a number changed, not whether it is right. Changes are expected when data changes. Known-answer tests on fixed rows isolate logic, so a failure always points to the model.