Power BI Row-Level Security: Dynamic vs Static Patterns

Row-Level Security (RLS) is the feature most Power BI teams deploy late and regret deploying late. The first time a regional sales manager opens a "national" dashboard and sees deals from a region they should not see, you have a credibility problem that takes months to undo. RLS done right is invisible: every viewer sees only their slice, every export honors the filter, and every external embed enforces the rule per tenant.

This article walks through static and dynamic RLS, when to choose each, the USERPRINCIPALNAME pattern that makes manager hierarchies work, and the testing discipline that catches the silent-failure mode where RLS appears to work but actually filters nothing. We end with embedded RLS — the pattern you need if you are putting Power BI inside your SaaS.

Key Takeaways

• RLS evaluates DAX filter expressions per role, returning the rows that match — incorrect filter logic means users either see nothing or see everything.
• Static RLS hardcodes one role per group (Sales-East, Sales-West) — simple, but unmaintainable past 10 roles.
• Dynamic RLS uses a security table joined to dimensions and USERPRINCIPALNAME() to filter rows per logged-in user — scales to thousands of users.
• USERPRINCIPALNAME() returns the Microsoft Entra ID UPN of the viewer; pair with a Users table that maps UPN to allowed entities.
• Manager hierarchies use the PATH / PATHCONTAINS pattern to grant managers visibility to all reports under them.
• Power BI Desktop's "View as role" feature is essential — use it before every publish.
• Embedded RLS via service principal + effectiveIdentity is the only safe way to enforce per-tenant isolation in SaaS.
• RLS does NOT secure the underlying dataset — users with Build permission on a dataset can bypass RLS by writing their own DAX. Use Workspace roles correctly.

---

How Power BI RLS Actually Works

RLS in Power BI is implemented as DAX filter expressions attached to roles. When a user views a report:

1. Power BI looks up which roles the user is a member of (in the Power BI Service workspace settings).
2. For each role, Power BI evaluates the table filter DAX and applies it as a CALCULATE filter to every visual.
3. Multiple roles are combined with logical OR — a user in two roles sees the union of both filtered rowsets.

The DAX you write returns TRUE for rows the user should see. Common mistake: returning a single value instead of TRUE/FALSE. The role then silently filters nothing because Power BI cannot evaluate the expression.

RLS is filter-based, not column-based. There is no row obfuscation, no column masking, no row counts that don't match. The user simply does not see the rows. If a region has zero rows after RLS, a sum measure shows (blank), and a totals card reads 0.

Static RLS: The Beginner Pattern

Static RLS hardcodes the filter per role. It is simple, fast to implement, and unmaintainable past about 10 roles.

Use case: You have 4 sales regions and want each Regional VP's team to see only their region.

In Power BI Desktop → Modeling → Manage roles, create:

Publish the dataset. In Power BI Service → Dataset → Security, assign Microsoft Entra ID groups to each role. The "Sales-East" group gets the East region only, and so on.

Why static breaks: every new region requires a model change, a republish, and an admin assignment. By role 20 your security model is unauditable.

Dynamic RLS: The Production Pattern

Dynamic RLS uses a security table that joins the user's UPN (User Principal Name) to the entities they are allowed to see. One role serves all users.

Step 1: Build the security table

Create a table — call it UserAccess — with these columns:

This table can come from your HRIS, Active Directory, a SharePoint list, or a database view. The key requirement: it is refreshed before the dataset refresh.

Step 2: Join to your fact dimension

Create a relationship: UserAccess[region] → Region[region] (single-direction, single-cardinality). The fact table joins to Region already.

Step 3: Build one role with this DAX

Role name: User Table: Region Filter:

'Region'[region] IN
    CALCULATETABLE (
        VALUES ( 'UserAccess'[region] ),
        'UserAccess'[user_upn] = USERPRINCIPALNAME ()
    )

Now every authenticated user sees exactly the regions in their UserAccess rows. Add a region by inserting a row into UserAccess and refreshing the dataset. No model change. No republish.

Step 4: Bidirectional filter for cross-table coverage

If Region filters multiple fact tables (orders, leads, invoices), set the relationship from UserAccess to Region to bidirectional, OR repeat the RLS filter on each fact table. Bidirectional is cleaner; repeated filters perform better. We default to bidirectional except on very large fact tables (50M+ rows).

Manager Hierarchy Pattern

A common requirement: a sales manager sees their direct reports' regions plus their own. Their VP sees the manager's regions plus their own. Build this with PATH.

Step 1: Org table with parent_id

employee_id | upn              | manager_id
1           | [email protected]     | (null)
2           | [email protected]      | 1
3           | [email protected]     | 2
4           | [email protected]    | 3
5           | [email protected]    | 3

Step 2: Add PATH column

Org Path = PATH ( 'Employee'[employee_id], 'Employee'[manager_id] )

Org Path for rep1 = "1|2|3|4". For their manager = "1|2|3". For VP = "1|2".

Step 3: RLS filter that walks the hierarchy

VAR CurrentUserID =
    LOOKUPVALUE (
        'Employee'[employee_id],
        'Employee'[upn], USERPRINCIPALNAME ()
    )
RETURN
    PATHCONTAINS ( 'Employee'[Org Path], CurrentUserID )

This filter returns every employee whose path contains the current user's ID — meaning, every employee in or below the current user's tree. Apply this on the Employee table; if your fact data joins to Employee, the filter cascades.

The CEO sees everyone. The VP sees themselves and below. The manager sees themselves and their two reps. Each rep sees only themselves. One role, one DAX expression, scales to any org chart.

Testing RLS Before You Publish

The silent-failure mode of RLS is what burns teams. The role appears to work, you publish, and it filters nothing because the DAX evaluates to a non-boolean.

Always use Power BI Desktop's "View as role" feature:

1. Modeling tab → View as → check the role(s) → optionally enter a UPN to test as a specific user.
2. The model recalculates with the filter applied.
3. Visual totals, slicers, and visuals all reflect the filtered rowset.

Test cases to run:

• A user who should see all rows (admin / unrestricted role).
• A user with one allowed entity.
• A user with multiple allowed entities.
• A user with NO row in the security table (should see nothing, not everything).
• A user who should see nothing (empty security mapping).
• An OR combination (manager + their own region).

If any test returns wrong rows, fix before publishing. We have seen production incidents where "no row in security table = sees everything" because the DAX used OR incorrectly.

RLS Across Multiple Tables

A common mistake: writing RLS on sales_order[region] directly when region is in a dimension. This works but locks future-you out of cross-table filtering.

Correct pattern: filter the dimension table only. Let model relationships propagate.

[ UserAccess ] → [ Region ] → [ Sales Order ]
                            → [ Lead ]
                            → [ Invoice ]

One RLS filter on Region covers Sales, Leads, and Invoices automatically. Maintain in one place.

Static vs Dynamic Comparison

Embedded RLS for SaaS Multi-Tenancy

Embedded analytics (Power BI Embedded A-SKUs) requires per-tenant isolation. The pattern uses a service principal and effectiveIdentity.

Architecture

1. Your SaaS web app authenticates the user (their identity).
2. Your backend calls Power BI Embedded REST API as a service principal.
3. The backend generates an embed token with effectiveIdentity specifying the username and roles.
4. The frontend renders the report; Power BI applies RLS as if the specified identity were viewing.

Sample backend code (Node.js / NestJS)

import { PowerBIEmbedded } from '@azure/arm-powerbiembedded';
import * as msal from '@azure/msal-node';

async function generateEmbedToken(reportId: string, tenantId: string, userId: string) {
  const accessToken = await acquireServicePrincipalToken();

  const body = {
    accessLevel: 'View',
    identities: [
      {
        username: `${userId}@${tenantId}`,
        roles: ['User'],
        datasets: [datasetId],
        customData: tenantId, // available as CUSTOMDATA() in DAX
      },
    ],
  };

  const response = await fetch(
    `https://api.powerbi.com/v1.0/myorg/groups/${workspaceId}/reports/${reportId}/GenerateToken`,
    {
      method: 'POST',
      headers: {
        Authorization: `Bearer ${accessToken}`,
        'Content-Type': 'application/json',
      },
      body: JSON.stringify(body),
    },
  );

  return response.json();
}

RLS DAX for embedded multi-tenant

-- In the Power BI dataset, role: "User"
'Tenant'[tenant_id] = CUSTOMDATA ()

CUSTOMDATA() returns the value passed in effectiveIdentity.customData. Each tenant's data is filtered to their tenant_id. This is the production pattern for SaaS embedded analytics.

Common RLS Pitfalls

1. RLS does not apply to dataset owners or workspace admins. When you "View as role" everything looks right, but you publish and the workspace admin sees everything. Test with a non-admin viewer.
2. Build permission bypasses RLS. A user with Build permission on a dataset can connect from Excel/Power BI Desktop and write their own DAX, ignoring roles. Only grant Build to trusted authors.
3. Bidirectional relationships can leak. A bidirectional relationship from a high-cardinality fact to a small dimension can let a filter "escape" through another fact table. Map your relationships and test.
4. Service refresh accounts need their own role. If you refresh a dataset via a service account, that account has no row in UserAccess and the refresh sees no data. Add a service-account-only role with no filter.
5. DAX errors evaluate to FALSE silently. A typo in a column name will not error — Power BI returns no rows. Always test with View as role.
6. Don't filter the security table itself. The security table should be excluded from RLS (no role on it), or it will filter itself and return nothing.

Operating RLS at Scale

After deployment:

• Document each role's DAX filter in a wiki — auditors will ask.
• Schedule quarterly access reviews where each manager re-confirms their team's RLS membership.
• Pipe UserAccess table changes through a ticketing system, not direct database edits.
• Monitor dataset refresh logs for RLS failures.
• Build a "no-data" dashboard page that shows a polite message when a viewer sees zero rows — better than blank visuals.

Frequently Asked Questions

What is the difference between Object-Level Security (OLS) and RLS?

RLS hides rows. OLS hides columns or entire tables. OLS was added in Power BI Premium and is configured via XMLA endpoint or Tabular Editor. Use OLS for sensitive columns (salaries) within an otherwise-shared dataset, and RLS for partitioning rows.

Does RLS work with paginated reports?

Yes. Paginated reports honor RLS on the underlying dataset when published to a Premium or PPU workspace. The User!UserID field in paginated reports maps to the same UPN.

Can I use RLS with DirectQuery?

Yes, but the RLS filter is sent to the source database on every query. Make sure the source can handle the additional WHERE clauses. For SQL Server / Synapse / Snowflake, this is fine. For sources without good index support, RLS can degrade DirectQuery performance.

How does RLS interact with Power BI sensitivity labels?

They are complementary. RLS filters which rows a user sees. Sensitivity labels classify the data and control export/share permissions. A "Highly Confidential" label can prevent screenshot or copy, while RLS ensures the user only saw their permitted rows in the first place. Use both for regulated workloads.

Where can I get help implementing RLS for our Odoo + Power BI deployment?

ECOSIRE deploys RLS for Odoo + Power BI clients regularly. We have field-tested patterns for multi-company isolation, sales territory hierarchies, and embedded multi-tenant SaaS. Reach out via our Power BI dashboard service or browse Power BI products for templates that include RLS scaffolding. For end-to-end implementation, see our Power BI implementation service.

---

RLS is the difference between a Power BI deployment that survives an audit and one that does not. Build the security table early, test with View as role on every change, and never give Build permission casually. The patterns above are the same ones we deploy in production for clients ranging from 50-user finance teams to multi-tenant SaaS with thousands of customers.