Weaving DataOps into Microsoft Fabric - DAX Query View Testing Pattern

The last three months of Power BI Desktop releases have made this DataOps fanatic giddy. The introduction of DAX Query View has laid the foundation to easily couple your tests with your semantic model.

Since Part 4 on my series, I have demonstrated ways to use DAX to test your semantic model, but this required making sure you meticulously organized your DAX files in a folder structure with your Power BI files. In addition, running these tests required 1) Opening your tool of choice (e.g., DAX Studio, SSMS) 2) Connecting to your local model 3) Opening the DAX file and 4) Running the test (example in Figure 1).

Figure 1 - An example of test cases and their output.

DataOps stresses reducing cycle times and manual tasks… DAX Query View cuts those steps in half. You simply open DAX Query view within Power BI Desktop and run the test (Figure 2).

Figure 2 - Example of running tests in DAX Query View within Power BI Desktop

Introducing a Pattern

In the world of actual, tangible fabrics, a pattern is the template from which the parts of a garment are traced onto woven or knitted fabrics before being cut out and assembled. I would like to take that concept and introduce a pattern for Microsoft Fabric, the DAX Query View Testing Pattern.

My hope is that with this pattern you have a template to weave DataOps into Microsoft Fabric and have a quality solution for your customers.

Why Test?

The hope-and-pray approach to publishing Power BI artifacts is counter to the DataOps mindset. Testing serves as the safety net that prevents your team from introducing errors in production. Testing also serves to identify issues in production proactively.

A customer is more likely to trust you if you come to them with a statement like: “We found an issue in production and we are working on a fix. It impacts this group of people, and I will give you an update in 30 minutes.” As opposed to getting a phone call from a customer stating: “There is an issue in production, are you aware of it?”. Testing allows for the former scenario to more likely occur than the later.

Now I say this knowing that testing only shows the presence of flaws, not the absence. However, if you can empirically show that what your team builds is founded on good testing practices, you have more legitimacy when defending your work. Testing defends against errors and defends against eventual scrutiny you will receive.

How to Test?

To follow the DAX Query View Testing Pattern you must follow these steps:

1) Setup Workspace Governance

2) Standardize Schema and Naming Conventions

3) Build Tests

Setup Workspace Governance

To get started we need to distinguish tests by their intended Power BI or Fabric workspace. This requires instituting workspace governance. You should at a minimum have two workspaces, one for development (DEV) and one for production (PROD). For larger projects, you should have a workspace for clients/customers to test (TEST) before moving to production. If you are unfamiliar with the concept please read this wiki article.

Your DEV workspace should have a static set of data (preferrable using parameters) to have a stable state with which you can build your tests. To test effectively you need to have a known underlying set of data to validate your semantic model. For example, if your upstream data is Fiscal Year-based, you could parameterize your tests to look at a prior Fiscal Year where the data should be stable. The goal is to have a static set of data to work with, so the only variables that would change during a test is the code you or your team has changed in Power BI.

Your TEST/PROD workspace is not static and considered live. Tests in this workspace are looking to conduct health checks (is there data in the table?) and identify data drift.

Standardize Schema and Naming Conventions

With workspace governance in place, you then need to institute two standards when building tests:

1) Standard Output Schema - In this pattern all tests should be based on a standard tabular schema as shown in Table 1.

Table 1 – Schema for test outputs

Column Name	Type	Description
TestName	String	Description of the test being conducted.
ExpectedValue	Any	What the test should result in. This should be a hardcoded value or function evaluated to a Boolean.
ActualValue	Any	The result of the test under the current dataset.
Passed	Boolean	True if the expected value matches the actual value. Otherwise, the result is false.

2) Tab Naming Conventions - Not only do we have a standard schema for the output of our tests, but we also make sure names of your tabs in the DAX Query View have some organization. Here is the naming format I have started to use:

[name].[environment].test(s)

• [name] is no more than 15-20 characters long. DAX Query View currently expands the tab name to fit the text, but we want to be able to tab between tests quickly.

• [environment] is either DEV, TEST, PROD and represents the different workspaces to run the test against. ALL is used where the same test should be conducted in all workspaces.

• Finally, the suffix of “.tests” or “.test” helps us distinguish what is a test file versus working files.

Build Tests

With this standard schema and naming convention in place, you can build tests covering three fundamental areas:

Testing Calculations

Calculated Columns and Measures should be tested to make sure theybehave as intended and handle edge cases. For example, let us say you have a DAX measure:

“IF(SUM(‘TableX’[ColumnY])<0,”Condition 1”,”Condition 2”)”

To test properly, you should create conditions to test when:

a. The summation is > 0

b. The summation is = 0

c. The summation is < 0

d. The summation is blank

Figure 3 - Example of tests for calculations like DAX measures and calculated columns.

Testing Content

Knowing that your tables and columns have the appropriate content is imperative. If you ever accidentally kept a filter in Power Query that was only intended for debugging/developing, you know testing content is important. Here are some tests you could run with this pattern:

• The number of rows in a fact table is greater than or equal to a number.
• The number of rows in a dimension is not zero.
• The presence of a value in a column that shouldn’t be there.
• The existence of blank columns.
• The values in a custom column are correct.

Figure 4 - Example of testing content of your tables and columns.

Note: Regex expressions still cannot be run against content in columns within DAX syntax. I have an alternative approach to that in this article.

Testing Schema

With the introduction of INFO functions in DAX, testing the schemas of your semantic model is finally that much easier. Schema testing is important because it helps you avoid two common problems (1) Broken visuals and (2) Misaligned Relationships.

Changing names with columns and DAX measures can break visuals that expect the columns spelt a certain way. This is especially troublesome if you have one dataset and multiple reports or report authors.

In addition, with a click of a button you can change a column from numeric to text. That may seem benign but what if a relationship with that column was with another table’s numeric column? You will have issues, and it is not an easy one to figure out (trust me, I wasted hours trying to resolve an issue only to realize this was the root problem).

So, to test schemas, you need to establish a baseline schema for each table. Luckily I have a template for that. This DAX code will generate the schema for you once you enter in the table name. Then you build the test (see Figure 5).

Subsequently, if this test fails you know you either intended to change the schema and need to update the test OR you did not intend the change to happen and you need to fix your model.

Figure 5 - Example of running tests against your semantic model’s schema.

Now you may be asking which tests are intended for DEV and which tests are intended for TEST/PROD? Well, the easy-out is it depends on your data, but Table 2 is my rule of thumb.

Table 2 - Rule of Thumb of Types of Tests for each Workspace.

Workspace	DEV	TEST	PROD
Testing Calculations	X
Testing Content	X	X	X
Testing Schema	X	X	X

Examples

Looking for a template of tests? Check out my Fabric DataOps pattern repository on GitHub for a sample model and sets of tests you can leverage in building your own tests. Also don’t forget to leverage the Power BI Performance Analyzer to copy DAX queries from visuals. It helps you build test cases quicker, avoid syntax errors, and understand DAX a little better (a win, win all around).

What’s Next?

With this pattern in place, you can build tests right in your semantic model, have your teams run them within the comfort of Power BI Desktop, and avoid introducing errors for your customers. But John, you may ask, “doesn’t that mean I have to make sure my team and I run each test manually?” The answer is Yes and No. Yes, when you want to validate some tests before publishing it is good practice to run the tests. But also, no, because with this DAX Query View Testing Pattern we now can leverage another feature, PBIP, to embrace orchestration.

I’ll cover that in my next article.

As always, let me know what you think on LinkedIn or Twitter/X.