# Stop Shipping Broken Semantic Models: Automate Power BI Tests with UDFs

> Learn how to build automated tests for Power BI semantic models using User-Defined Functions, PQL.Assert, Power Automate, and Fabric Notebooks.

- Canonical URL: https://coreiten.com/en/article/stop-shipping-broken-semantic-models-automate-power-bi-tests-with-udfs
- Language: en
- Section: Excel
- Author: Sami
- Published: 2026-06-03T02:04:44+03:00
- Modified: 2026-06-03T02:05:54+03:00
- Publisher: CoreITen (https://coreiten.com)
- Keywords: Power BI automated testing, User-Defined Functions, PQL.Assert, DAX measures, Fabric Notebooks, Power Automate, semantic models

---

Broken semantic models and faulty DAX measures can silently ruin Power BI reports before anyone notices. Historically, ensuring consistent testing conventions required tedious manual reviews and copying DAX measures across projects. That changed in late 2025 when Microsoft introduced [User Defined Functions (UDFs) for Power BI](https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-user-defined-functions-overview), allowing developers to build reusable, automated tests.

By combining UDFs with the open-source library PQL.Assert, data teams can catch duplicate rows, broken relationships, and filter context errors before they reach production. This guide details how to standardize testing across your team and automate execution using Power Automate or Fabric Notebooks.

### How to Enable UDFs and Import PQL.Assert

Before building tests, you must enable UDFs, which are currently a preview feature, and import the testing library.

- **Open** Power BI Desktop and navigate to File > Options and Settings > Options. *This accesses the core application settings.*
- **Select** Preview Features, check the box for User Defined Functions, and restart the application. *This activates the ability to write and save custom functions.*
- **Copy** the TMDL script for PQL.Assert from DAXLib. *This open-source library provides a comprehensive set of assertion functions, similar to how [Python has PyPI](https://pypi.org/).*
- **Navigate** to the TMDL View in your model, create a new tab, paste the script, and click Apply. *This embeds the testing framework directly into your semantic model.*

### Building Your First Automated Test

Once PQL.Assert is imported, you can write tests to validate data quality, such as ensuring upstream SQL providers do not inject duplicate rows.

1. **Create** a new tab in the DAX Query View and name it using the convention `[name].[environment].test(s)` (e.g., `DataQuality.ANY.Tests`). *This standardizes test discovery across environments.*
2. **Define** the test using a `DEFINE FUNCTION` block and call a PQL.Assert function. *This establishes the specific condition the data must meet.*

```dax
DEFINE
    FUNCTION DataQuality.ANY.Tests = () =>
    PQL.Assert.Col.ShouldBeDistinct("Verify AlignmentID is distinct", 'AlignmentDim'[AlignmentID])
EVALUATE DataQuality.ANY.Tests()
```

To test multiple columns simultaneously, wrap the assertions in a `UNION` function:

```dax
DEFINE
    FUNCTION DataQuality.ANY.Tests = () =>
    UNION(
        PQL.Assert.Col.ShouldBeDistinct("Verify AlignmentID is distinct", 'AlignmentDim'[AlignmentID]),
        PQL.Assert.Col.ShouldBeDistinct("Verify Eye Color is distinct", 'EyeColorDim'[EyeID])
    )
EVALUATE DataQuality.ANY.Tests()
```

Click Run to execute the test. The results will return a standard schema detailing the test name, expected outcome, actual outcome, and a boolean pass/fail status. Clicking "Add to Model" ensures the test remains with the dataset for future validation.

### Automating Tests with Power Automate

For continuous monitoring, you can automate test execution in the Power BI service using a pre-built Power Automate flow. This approach uses V1 functions and is ideal for standard semantic models.

1. **Download** the solution ZIP file from [the examples/power-automate folder](https://github.com/clientfirsttech/PQL.Assert/tree/main/examples/power-automate). *This provides the pre-configured automation logic.*
2. **Import** the solution into Power Automate following Microsoft’s [import guide](https://learn.microsoft.com/en-us/power-apps/maker/data-platform/import-update-export-solutions). *This integrates the flow into your tenant.*
3. **Set** the environment variables for your Workspace GUID and Semantic Model ID when prompted. *This directs the flow to the correct Power BI assets.*

Once configured, the flow retrieves tests using `PQL.Assert.RetrieveTestsByEnvironment`, executes them, and evaluates the results. You can extend this flow to save results to [SharePoint](https://www.microsoft.com/en-gb/microsoft-365/sharepoint/collaboration) or trigger Microsoft Teams alerts.

### Executing RLS Tests via Fabric Notebooks

The Power Automate method does not support Row-Level Security (RLS) testing. To validate RLS rules via user impersonation, you must use a [Fabric Notebook](https://learn.microsoft.com/en-us/fabric/data-engineering/how-to-use-notebook).

1. **Add** the `PQLAssert_ImpersonatedUserName` annotation to your test function in the TMDL view. *This instructs the system which user identity to simulate during the test.*

```dax
createOrReplace
    function 'RLS.ANY.Tests' =
            () =>
                    PQL.Assert.ShouldEqualExactly("West Security Group Should Not See Other Groups",0,COUNTROWS(FILTER('Groups',Groups[Group Name]"West"))+0)
        lineageTag: de968091-8b67-454c-bfa9-61fa543cbdad
        annotation PQLAssert_ImpersonatedUserName = user@contoso.com
```

1. **Download** the `RunPQLAssertTests.ipynb` notebook from [the examples/fabric-notebook folder](https://github.com/clientfirsttech/PQL.Assert/tree/main/examples/fabric-notebook) and upload it to your workspace. *This acts as the multi-workspace test runner.*
2. **Update** the workspace GUIDs in Step 3 of the notebook. *This targets the specific environments you want to scan.*

```python
WORKSPACE_GUIDS: list[str] = [
    "00000000-0000-0000-0000-000000000002"  # Replace with your workspace GUID
]
ENVIRONMENT: str = "ANY"  # Options: "DEV" | "TEST" | "PROD" | "ANY" | ""
```

The notebook uses the `semantic-link-labs` package to discover models, read the impersonation annotations, and execute tests as specific users. This guarantees that your security rules function exactly as intended in production.

### The Engineering Evolution of Power BI

The introduction of User-Defined Functions fundamentally shifts Power BI from a pure visualization layer into a mature software engineering environment. By treating DAX and Power Query as code that requires rigorous, automated testing, organizations can finally implement true DataOps practices. The reliance on manual validation is no longer a necessary bottleneck.

However, the divide between automation methods highlights a critical architectural choice for enterprise teams. While Power Automate offers a low-barrier entry for basic testing, its inability to handle Row-Level Security impersonation makes it insufficient for complex deployments. Fabric Notebooks emerge as the mandatory standard for enterprise governance, proving that Microsoft is positioning Fabric as the non-negotiable backbone for secure, scalable data operations.

## Sources

- [red-gate.com](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/)