Get Help

Test Power BI

CAT tests the numbers in a Power BI model the way it tests a database: a query, an expectation, and — the part that matters — a comparison with the warehouse the model was built from. Here is what you can test, how to set it up (easiest in CAT Studio), and which tests pay off.

What you can test

Where the model is Provider Guide
A .pbix or .pbip file open in Power BI Desktop on your machine PowerBI@2 — the open file’s name is the whole connection Test a Power BI Desktop file
A semantic model published to a workspace — Power BI or Microsoft Fabric Dax@2 over the workspace’s XMLA endpoint Test a semantic model in a workspace
A tabular model in Azure Analysis Services or SQL Server Analysis Services Dax@2 the same guide — only the connection string differs

In every case the test’s query is DAX, not SQL — a tabular model does not speak SQL. You need a little DAX, and there are three easy ways to get the query you need without writing it: Get the DAX query behind a visual. DAX for SQL people covers the rest.

Set it up in CAT Studio

The quickest way to a working Power BI data source is CAT Studio: Data sources → New data source, pick the technology — Power BI Desktop, Power BI Semantic Model, MS Fabric Semantic Model, Azure Analysis Services, SQL Server Analysis Services — and the dialog asks for exactly the values that technology needs (the open file’s name; the workspace, the model, the service principal) and writes the connection string for you. On the Tests page the query editor runs DAX against it, so you see the result of your query before you wrap an expectation around it. CAT Pilot — Studio’s AI assistant — writes the DAX for you from a sentence, and turns a query into a named, described, tagged test.

Everything Studio writes is an ordinary project file, so the same tests run from the command line and in pipelines. Without Studio, the two guides above show the YAML.

Which tests pay off

A Power BI model is a copy of data that lives somewhere else, shaped by measures someone wrote. The tests worth having check exactly those two things:

  • The model matches its source. A sets match test with the DAX on one side and the SQL on the other — customers in the model against customers in the warehouse, sales per month in the model against sales per month in the source. This is the test only a tool that speaks both DAX and SQL can run, and it catches the refresh that did not happen, the filter somebody left in Power Query, the relationship that drops rows.
  • The measures are right. Total sales from the measure against SUM from the warehouse, per year, with a Tolerance for rounding; a measure that must never be blank; the number a visual shows, taken straight from the report with Performance Analyzer, against the number the business expects. See Test measures.
  • The model is complete. Every dimension loaded (set is not empty), every expected member present, no orphan keys — smoke tests that take a minute to write and run on every refresh.

Test the file while you build it, test the published model on a schedule — the project file is the same; only the data source changes.

← Run tests in parallel
Test a Power BI Desktop file →