---
title: "Test measures"
description: "Worked examples for testing Power BI measures — a total against the warehouse, a measure per year with a tolerance, a measure that must not be blank, a number against the value the business expects"
url: "https://docs.justcat.it/how-to-guides/data-platforms/power-bi/test-measures/"
---
# Test measures


All examples use a model data source `sales model` ([Desktop file](https://docs.justcat.it/how-to-guides/data-platforms/power-bi/test-a-power-bi-desktop-file/ "Test a Power BI Desktop file") or [workspace](https://docs.justcat.it/how-to-guides/data-platforms/power-bi/test-a-semantic-model-in-a-workspace/ "Test a semantic model in a 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](https://docs.justcat.it/reference/tests/order-and-key/ "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:


**Properties**



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





**YAML**


```yaml
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:


**Properties**



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
: ```sql
  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





**YAML**


```yaml
- 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


**Properties**



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





**YAML**


```yaml
- 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](https://docs.justcat.it/how-to-guides/data-platforms/power-bi/get-the-dax-query-behind-a-visual/ "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:

```yaml
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](https://docs.justcat.it/reference/data-sources/providers/excel-2/ "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](https://docs.justcat.it/reference/tests/templates/ "Templates") with the list of measure names as metadata generates one test per measure; see [Generate tests from metadata](https://docs.justcat.it/how-to-guides/organize-and-run-tests/generate-tests-from-metadata/ "Generate tests from metadata").

## Related

* [Test Power BI](https://docs.justcat.it/how-to-guides/data-platforms/power-bi/test-power-bi/ "Test Power BI") · [Get the DAX query behind a visual](https://docs.justcat.it/how-to-guides/data-platforms/power-bi/get-the-dax-query-behind-a-visual/ "Get the DAX query behind a visual")
* [Sets match](https://docs.justcat.it/reference/tests/expectations/sets-match/ "Sets match") · [Tolerance](https://docs.justcat.it/reference/tests/tolerance/ "Tolerance") · [Order and key](https://docs.justcat.it/reference/tests/order-and-key/ "Order and key")