Test measures
A measure is a formula somebody wrote; the only proof it is right is a number computed another way. These tests compute it another way — in the warehouse, in a spreadsheet, in the business's head — and let CAT compare.
All examples use a model data source sales model (Desktop file or workspace — the tests do not care) and, where a second side is needed, a warehouse data source DWH. Both sides of every sets match are ordered — ORDER BY in DAX and SQL alike — or the test sets Sort data: true; see Order and key.
A total against the warehouse
One row on each side. Tolerance absorbs rounding between the model’s decimal and the database’s:
Tests:
- Name: Total sales equals the warehouse
Suite: Measures
First data source: sales model
First query: EVALUATE ROW("Total Sales", [Total Sales])
Second data source: DWH
Second query: SELECT SUM(Amount) FROM fact.Sales
Expectation: sets match
Tolerance: 0.01
A measure per dimension member
The test that finds the month somebody filtered out. Key: 1 pairs the rows by the first column, so a failure names the year and shows both values side by side:
- Name: Sales per year equal the warehouse
Suite: Measures
First data source: sales model
First query: |
EVALUATE SUMMARIZECOLUMNS('Date'[Year], "Sales", [Total Sales])
ORDER BY 'Date'[Year]
Second data source: DWH
Second query: |
SELECT YEAR(OrderDate) AS [Year], SUM(Amount) AS Sales
FROM fact.Sales GROUP BY YEAR(OrderDate) ORDER BY [Year]
Expectation: sets match
Key: 1
Tolerance: 0.01
Maximum errors logged: 20
Percent tolerance when the sides differ by a ratio, not an amount: Tolerance: 0.5 with Tolerance mode: percent.
A measure that must never be blank
- Name: Total sales is not blank
Suite: Measures
Data source: sales model
Query: EVALUATE FILTER(ROW("v", [Total Sales]), ISBLANK([v]))
Expectation: set is empty
FILTER keeps the row only when the measure is blank; an empty set is the pass. The same shape catches a negative margin, a ratio above 100 %, a date in the future.
The number the user sees, against what the business expects
Take the visual’s query with Performance Analyzer (Copy query — see Get the DAX query behind a visual) and compare it with a table of expected values that lives right in the project file, served by the Yaml@1 provider:
Data sources:
- Name: expected
Provider: Yaml@1
Connection string: Sales.cat.yaml # this very file
Tests:
- Name: Revenue by region matches the sign-off
Suite: Measures
First data source: sales model
First query: |
EVALUATE SUMMARIZECOLUMNS('Region'[Region], "Revenue", [Revenue])
ORDER BY 'Region'[Region]
Second data source: expected
Second query: /Expected revenue
Expectation: sets match
Key: 1
Tolerance: 1
Expected revenue:
- Region: EMEA
Revenue: 1250000
- Region: Americas
Revenue: 980000
The expected table can just as well be an Excel sheet the business maintains (Excel@2) — then the people who know the numbers own the test’s truth.
Many measures, one template
When the same check applies to every measure — not blank, within a range — one template with the list of measure names as metadata generates one test per measure; see Generate tests from metadata.