{"id":110320,"date":"2026-06-02T12:00:00","date_gmt":"2026-06-02T12:00:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=110320"},"modified":"2026-05-28T14:51:08","modified_gmt":"2026-05-28T14:51:08","slug":"never-ship-a-broken-semantic-model-again-how-to-build-automated-tests-in-power-bi-with-user-defined-functions","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/business-intelligence\/powerbi\/never-ship-a-broken-semantic-model-again-how-to-build-automated-tests-in-power-bi-with-user-defined-functions\/","title":{"rendered":"Never ship a broken semantic model again: how to build automated tests in Power BI with user-defined functions"},"content":{"rendered":"\n

User-defined functions (UDFs) in Power BI let you build reusable, automated tests for your semantic models \u2014 so you catch broken measures, duplicate rows, and faulty relationships before they reach your reports. This article walks through how to use UDFs alongside PQL.Assert to standardize testing across your team, and how to automate those tests with Power Automate or a Fabric Notebook.<\/strong><\/p>\n\n\n\n

Reuse<\/strong> is a very important term in DataOps<\/a>. It is defined as the practice of leveraging existing components, code, or processes across multiple projects to reduce redundancy and improve consistency.<\/p>\n\n\n\n

However, when it comes to Power BI<\/a>, reusing DAX<\/a> measures across projects was a difficult ‘copy and paste’ job. For my teams, we used DAX measures to help with testing our semantic models<\/a>, but ensuring consistent testing conventions (and standard schemas of the tests) required lots of manual review. <\/p>\n\n\n\n

Thankfully, that changed in late 2025 when Microsoft introduced User Defined Functions (UDFs) for Power BI<\/a>. In this article, I’ll demonstrate how to use UDFs for testing, plus how to standardize the way teams test their models.<\/p>\n\n\n\n

Why is it important to test semantic models?<\/h2>\n\n\n\n

Many folks ask me, \u201cwhy would you test your model?\u201d<\/em> Humans are fallible, so the semantic models we build are fallible as well. It’s easy to write a DAX measure that doesn\u2019t properly handle filter context and results in inaccurate summations when a slicer filters the model. <\/p>\n\n\n\n

It’s also easy to leave a filter in a step in Power Query<\/a> when debugging your transformations, resulting in rows being accidentally removed from the model (not that I have ever done that!)<\/p>\n\n\n\n

We should not only test to avoid repeating past mistakes, but for parts of the model we anticipate would have detrimental effects on reports. For example, columns used in relationships, often-used DAX measures, etc. <\/p>\n\n\n\n

From a technical perspective, DAX and Power Query are code<\/strong>. Yes, they are maybe somewhat abstracted by low-code tools, but code should be tested nonetheless<\/strong>.<\/p>\n\n\n\n

How to use user-defined functions for testing in Power BI<\/h2>\n\n\n\n

I’ll run through the process step-by-step. First, the setup. You\u2019ll need to specifically turn on UDFs, since it’s currently (as of March 2026), a preview feature<\/strong>:<\/p>\n\n\n

\n