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

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, 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.

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.
• 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.

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.

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:

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.

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. This provides the pre-configured automation logic.
2. Import the solution into Power Automate following Microsoft’s import guide. 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 or trigger Microsoft Teams alerts.

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.

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.

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 = [email protected]

2. Download the RunPQLAssertTests.ipynb notebook from the examples/fabric-notebook folder and upload it to your workspace. This acts as the multi-workspace test runner.
3. Update the workspace GUIDs in Step 3 of the notebook. This targets the specific environments you want to scan.

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 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.