Data Governance

Row Level Security in Power BI Without the Audit Findings Later

By Syed Hussnain Sherazi | September 2, 2025 | Power BI | RLS | Security

Enterprise row-level security patterns for Power BI models that need to survive audits and reorganisations.

Row level security looks straightforward in tutorials. You write a DAX filter, assign a few users to a role, publish the report, and you are done. In practice, the moment the model meets a real organisation with thousands of users, several legal entities, an HR hierarchy, and a managers can see their reports clause, the simple approach falls apart.

This article walks through the patterns that hold up at enterprise scale. It also covers the operational pieces that audits inevitably ask about, such as how the security definitions are tested, how exceptions are documented, and how the whole thing survives a reorganisation.

What Row Level Security Actually Does

Row level security in Power BI applies a DAX filter at query time based on who is asking. When a user opens a report, the engine evaluates the user identity, finds the roles that user belongs to, evaluates each role filter, and then applies the union of those filters to every visual on every page.

The mechanism is simple. The administration around it is where teams struggle. A typical organisation has at least four kinds of access requirements that need to coexist. Geographic access, where users see only their region. Functional access, where finance users see all geographies but only finance metrics. Hierarchical access, where managers see their team plus everyone below. And exceptions, where specific senior users see everything regardless of the rules.

Trying to handle all four with ad hoc DAX inside one role is the path to madness. The patterns below decouple the rules from the implementation.

Pattern 1, Static Roles for Stable Categories

When the access categories rarely change, static roles are the cleanest implementation. Create one role per category and assign users to it via group memberships in Microsoft Entra ID.

-- Role: EuropeUsers
[Region] = "Europe"

-- Role: AmericasUsers
[Region] = "Americas"

-- Role: APACUsers
[Region] = "APAC"

In the Power BI service, assign Microsoft Entra ID security groups to each role rather than individual users. When a new user joins the European team, they get added to the EU security group through the normal joiner mover leaver process, and Power BI access follows automatically.

The biggest mistake here is to assign individual users to roles. It works, but it does not scale, and it tends to drift over time as people change teams.

Pattern 2, Dynamic Roles for User Specific Access

When the rules depend on properties of the user, dynamic roles are required. The pattern uses USERPRINCIPALNAME to identify the requester at query time, then looks up their access in a security table.

Build a SecurityMapping table that joins users to the entities they are allowed to see.

UserPrincipalName, EntityID, AccessLevel
alice@contoso.com, 101, Full
alice@contoso.com, 102, Full
bob@contoso.com, 103, ReadOnly

Hide this table in the model, set its relationship to the relevant dimension, and write a single role.

-- Role: DynamicEntityAccess
[EntityID] IN
CALCULATETABLE(
    VALUES(SecurityMapping[EntityID]),
    SecurityMapping[UserPrincipalName] = USERPRINCIPALNAME()
)

Now the security definition lives in a table you can manage with the same tools as any other reference data. Adding a user to a new entity is a row insert, not a model change.

Pattern 3, Hierarchical Access for Manager Views

The manager pattern is the trickiest one. A manager should see their direct reports, plus everyone reporting up the chain, recursively. The naive approach is a recursive CTE in SQL and a flattened table that maps every manager to every report at every depth.

Build a flattened EmployeeHierarchy table with two columns. ManagerUPN and EmployeeID. For each employee, include one row for every ancestor up the chain. The table is denormalised on purpose, because DAX is bad at recursion but fast at lookup.

-- Role: HierarchicalAccess
[EmployeeID] IN
CALCULATETABLE(
    VALUES(EmployeeHierarchy[EmployeeID]),
    EmployeeHierarchy[ManagerUPN] = USERPRINCIPALNAME()
)

The flattening can be done in the source warehouse, in a Fabric Lakehouse, or in Power Query. The choice depends on where the canonical hierarchy lives. Whatever you choose, refresh the flattened view whenever the hierarchy changes, and never edit it by hand.

Pattern 4, Combining Multiple Filters

When multiple roles apply to the same user, Power BI takes the union of their filters. This is usually what you want, but it occasionally produces surprises.

Suppose Alice belongs to EuropeUsers and to FinanceUsers. EuropeUsers filters Region equals Europe. FinanceUsers filters Department equals Finance. Alice will see Region equals Europe OR Department equals Finance, which is broader than either filter alone. If you wanted the intersection, you would need to combine the filters inside a single role.

-- Role: EuropeFinance
[Region] = "Europe" && [Department] = "Finance"

Document this behaviour clearly. Auditors ask about it.

Pattern 5, Object Level Security for Truly Sensitive Columns

Row level security hides rows. Object level security hides columns or measures entirely. Use it when certain users should not even know that a particular column exists.

A common example is the Salary column on an HR dataset. Most users should not see it. Some senior HR users should. Object level security is configured through Tabular Editor or via the XMLA endpoint. The role definition uses a different syntax.

<Roles>
  <Role Name="StandardHR">
    <Members>...</Members>
    <TablePermissions Table="DimEmployee">
      <ColumnPermissions>
        <ColumnPermission Column="Salary" MetadataPermission="None" />
        <ColumnPermission Column="Bonus" MetadataPermission="None" />
      </ColumnPermissions>
    </TablePermissions>
  </Role>
</Roles>

Users in this role will not see Salary or Bonus in the field list, in the Q&A pane, or anywhere else. This is much stronger than hiding a column, which only removes it from the Reports view.

A Reference Architecture

flowchart LR
    AAD[Microsoft Entra ID Groups]
    Sec[SecurityMapping Table]
    Hier[EmployeeHierarchy Table]
    Model[Power BI Semantic Model]
    Workspace[Workspace with App]
    Users[End Users]

    AAD --> Workspace
    Sec --> Model
    Hier --> Model
    Model --> Workspace
    Workspace --> Users
    Users -.identity.-> Model

The clean separation matters. Security membership lives in Entra ID. Mapping tables live in the warehouse. Filter logic lives in DAX roles. Workspace access lives in the service. Each of these surfaces is owned by a different team in many organisations. Trying to merge them creates governance pain.

Testing Your Roles

The most common reason row level security breaks is that nobody tested it after a model change. Build the testing into the deployment pipeline.

The Power BI service exposes a View as feature in the desktop and in the workspace. You can impersonate a role and see what data is visible. Use this in development. For production, run automated tests that connect to the dataset using a service principal, switch into a role, and verify that a known set of queries returns the expected number of rows.

A simple harness in PowerShell or Python looks like this.

import requests

def test_role(workspace_id, dataset_id, role_name, user_upn, expected_rows, query):
    body = {
        "queries": [{"query": query}],
        "impersonatedUserName": user_upn,
        "serializerSettings": {"includeNulls": True}
    }
    response = requests.post(
        f"https://api.powerbi.com/v1.0/myorg/groups/{workspace_id}/datasets/{dataset_id}/executeQueries",
        headers={"Authorization": f"Bearer {token}"},
        json=body
    )
    actual = len(response.json()["results"][0]["tables"][0]["rows"])
    assert actual == expected_rows, f"Role {role_name} returned {actual} rows, expected {expected_rows}"

Run this on every model deploy. Failing tests block the deploy.

Common Mistakes That Auditors Spot

The first mistake is leaving the Build permission too wide. Users who can build new reports against the dataset can sometimes work around row level security through clever DAX or by exporting raw data. Restrict Build to a small group, and audit the membership.

The second is forgetting that workspace administrators bypass row level security. Anyone with admin rights on the workspace sees all rows. Keep workspace admin lists short and rotate them through a privileged access process.

The third is the export trap. Exports to Excel or CSV are governed by tenant settings. If exports are allowed and a user can see the visual, they can save the underlying data. For sensitive datasets, restrict exports through the tenant policy and through sensitivity labels.

The fourth is documentation drift. The DAX filter is the source of truth, but the audit will ask for a plain English description of who sees what. Keep that document in version control alongside the model. When the filter changes, the document changes in the same pull request.

Operating Row Level Security at Scale

When a model has hundreds of roles or a security mapping table with millions of rows, performance can suffer. Two patterns help.

Cache the security mapping table aggressively. Set its refresh policy to incremental, partition it by date, and only rebuild changed partitions. The table is small relative to fact data, but it is queried on every user session, so any latency compounds.

Avoid heavy filter logic inside the role. The DAX inside a role is evaluated for every query the user makes. Keep it as simple as possible. If complex logic is required, pre compute the result in the warehouse and represent it as a flat lookup.

The Conversation You Need With Compliance

Row level security in Power BI is strong enough for almost every regulated use case, but compliance teams will want specific assurances. Three questions come up consistently.

How is the access definition versioned and reviewed. The answer is that the role DAX lives in source control, that pull requests are reviewed, and that the security mapping table is loaded from a system of record with an audit trail.

How are exceptions handled. The answer is that there is a defined process for granting time bounded exceptions, that exceptions are logged, and that they expire automatically when the time bound passes.

How do you prove that a specific user sees only what they should. The answer is the automated test harness, the View as feature for ad hoc verification, and a quarterly access review where a sample of users is audited end to end.

A Closing Note

The teams that get row level security right treat it as a discipline rather than as a feature. They build it once, test it on every change, and review the model holistically every quarter. The teams that get it wrong treat each new requirement as a one off DAX edit, and they end up with thirty roles that nobody understands and nobody dares to delete.

Pick the discipline. It is far cheaper than the alternative.

References and Further Reading