Designing Robust Row-Level Security in Power BI: From Simple Roles to Multi-Tenant Models

Why RLS Design Matters More Than the Checkbox

RLS is not just a feature toggle you flip on at the end of a project. Poor RLS design shows up as:

• Reports that must be duplicated per region or customer.
• Hard-coded filters in roles that break every time the org structure changes.
• Confusing behaviour when users see partial data or wrong totals.

A robust design has:

• A clear security model (who should see what, and why).
• A central security table driving all filters.
• Minimal, reusable roles.
• Predictable behaviour when new users or tenants are added.

The rest of this article builds that step by step.

Core RLS Concepts (Without the 101)

You already know where RLS lives in Power BI Desktop (Modeling ➜ Manage roles). The parts that matter for robust design are:

• Filter direction: RLS filters follow the same relationship directions as any other filter.
• Static vs dynamic roles:
  • Static: role filters are fixed DAX expressions.
  • Dynamic: role filters depend on the current user (USERPRINCIPALNAME() or USERNAME()).
• Security tables: tables that map users to allowed entities (region, customer, tenant).
• Multi-tenant vs single-tenant: whether your model serves one organisation or many.

Once you think in these terms, you stop writing random filters in roles and start designing a security model.

Step 1: Start with Simple, Static Roles

For small scenarios, static roles are fine. Example: you have a Sales model and want:

• A Sales_EU role that only sees EU region.
• A Sales_US role that only sees US region.

Assume your DimRegion table has a RegionGroup column.

Example: Static Role Filter

In Manage roles, create a role Sales_EU and set this filter on DimRegion:

[RegionGroup] = "EU"

And for Sales_US:

[RegionGroup] = "US"

This works, but does not scale. Every new region means a new role. That’s acceptable for:

• Small teams with few regions.
• One-off reports with limited lifespan.

If you expect growth or multiple tenants, do not stay in this pattern.

Step 2: Move to Dynamic, User-Based RLS

Dynamic RLS centralises the logic in a single security table and uses the current user’s identity to filter.

Design the Security Table

Create a table, e.g. SecurityUserRegion, with columns like:

• UserPrincipalName (email / UPN).
• RegionKey (matching DimRegion[RegionKey]).
• Optionally: RoleType, IsManager, etc.

Sample structure:

CREATE TABLE SecurityUserRegion (
    UserPrincipalName NVARCHAR(256) NOT NULL,
    RegionKey INT NOT NULL,
    RoleType NVARCHAR(50) NULL
);

Load this into your model and relate it:

• SecurityUserRegion[RegionKey] ➜ DimRegion[RegionKey] (single-direction, SecurityUserRegion ➜ DimRegion).

Dynamic RLS DAX Rule

Create a role DynamicRegionRLS and set this filter on SecurityUserRegion:

SecurityUserRegion[UserPrincipalName] = USERPRINCIPALNAME()

How it works:

1. USERPRINCIPALNAME() returns the logged-in user.
2. The role filters SecurityUserRegion to rows for that user.
3. That filter propagates via relationships to DimRegion and then to fact tables.

You now have one role for all users. Adding a new user or region is a data operation (update SecurityUserRegion), not a model change.

Step 3: Role-Based Access (Managers vs Staff)

Most real setups need different levels of access:

• Staff: see only their region.
• Regional managers: see all regions in their country.
• Global: see everything.

You can keep this in the same security table.

Extend the Security Table

Add columns:

• AccessLevel (e.g. "Staff", "Manager", "Global").
• CountryKey (for manager-level filters).

DAX Pattern for Role-Based Logic

Filter on SecurityUserRegion with conditional logic:

VAR CurrentUser = USERPRINCIPALNAME()
VAR UserRows = FILTER(
    SecurityUserRegion,
    SecurityUserRegion[UserPrincipalName] = CurrentUser
)
RETURN

// Allow multiple rows per user; the table filter is the result
UserRows

If you need different behaviour per AccessLevel, you can move some logic into calculated columns or views upstream, so that the RLS rule remains simple: filter by user and let the relationships + modelling handle the rest.

Resist the temptation to write complex IF logic directly in the role definition. Complex branching belongs in the data model, not in the RLS expression.

Step 4: Multi-Tenant RLS – One Model, Many Customers

Multi-tenant means one dataset, multiple customers or business units, each seeing only their own data.

You typically have:

• DimTenant (Tenant / Customer dimension).
• Fact tables with a TenantKey.
• A security table mapping users to tenants.

Minimal Multi-Tenant Schema

Key tables:

• DimTenant(TenantKey, TenantName, Status, ...)
• FactSales(TenantKey, DateKey, ProductKey, ..., SalesAmount)
• SecurityUserTenant(UserPrincipalName, TenantKey, AccessLevel)

Relationships:

• SecurityUserTenant[TenantKey] ➜ DimTenant[TenantKey].
• DimTenant[TenantKey] ➜ FactSales[TenantKey].

All relationships should be single-direction from security ➜ dimension ➜ fact.

Dynamic Multi-Tenant RLS DAX

Role filter on SecurityUserTenant:

VAR CurrentUser = USERPRINCIPALNAME()
RETURN
FILTER(
    SecurityUserTenant,
    SecurityUserTenant[UserPrincipalName] = CurrentUser
)

This gives:

• Users mapped to one tenant: see only that tenant.
• Users mapped to multiple tenants: see all their tenants.

If you need tenant admins who see only their tenant but all users within it, keep that logic in your app or upstream identity system; RLS should stay focused on data rows, not user management.

Step 5: Combining Tenant and Org Hierarchy

In many setups, you need both:

• Tenant isolation (customer A must never see customer B).
• Internal segmentation (within tenant, restrict by region, department, etc.).

You can layer this without exploding complexity.

Security Model Pattern

Use two security tables:

1. SecurityUserTenant

   • UserPrincipalName
   • TenantKey

2. SecurityUserOrg

   • UserPrincipalName
   • TenantKey
   • OrgUnitKey or RegionKey
   • AccessLevel

Relationships:

• SecurityUserTenant[TenantKey] ➜ DimTenant[TenantKey].
• SecurityUserOrg[TenantKey] ➜ DimTenant[TenantKey].
• SecurityUserOrg[OrgUnitKey] ➜ DimOrgUnit[OrgUnitKey].

In the RLS role, set filters on both tables:

// On SecurityUserTenant
SecurityUserTenant[UserPrincipalName] = USERPRINCIPALNAME()

// On SecurityUserOrg
SecurityUserOrg[UserPrincipalName] = USERPRINCIPALNAME()

How it behaves:

• SecurityUserTenant ensures tenant isolation.
• SecurityUserOrg narrows within tenant by org unit / region.

Important: avoid bi-directional relationships here; they make RLS behaviour harder to reason about and debug.

Step 6: Handling Shared Dimensions and Cross-Tenant Tables

Multi-tenant models often share dimensions (e.g. DimDate, DimProduct) across tenants. That’s fine as long as:

• Fact tables are tenant-filtered.
• Shared dimensions are either:
  • Tenant-neutral (e.g. dates), or
  • Tenant-aware via a TenantKey.

Tenant-Neutral Dimensions

DimDate is usually tenant-neutral. It should not be filtered directly by RLS. Instead, RLS filters facts, and those facts filter dates.

Tenant-Aware Dimensions

For dimensions like DimProduct, you might have:

• TenantKey on DimProduct.
• Relationship: DimTenant ➜ DimProduct ➜ FactSales.

This ensures that when a user’s tenant is filtered, they only see that tenant’s products and facts.

Watch Out for Shared Reference Tables

Common mistakes:

• A reference table (e.g. DimCurrency) used by multiple tenants but not tenant-filtered.
• Measures that aggregate across all tenants because the dimension is not constrained.

Mitigation options:

• Add TenantKey to shared dimensions where it makes sense.
• Use measures that explicitly filter by tenant when needed:

Total Sales (Tenant) =
VAR CurrentTenant = SELECTEDVALUE(DimTenant[TenantKey])
RETURN
CALCULATE(
    SUM(FactSales[SalesAmount]),
    DimTenant[TenantKey] = CurrentTenant
)

Step 7: Testing and Debugging RLS Properly

RLS bugs are often subtle. A few practical habits:

1. Use “View as” with Multiple Users

In Power BI Desktop:

• Use View as.
• Test with:
  • A typical user.
  • A manager.
  • A multi-tenant user.

Check:

• Row counts (e.g. total sales) match expectations.
• Filters in the filter pane behave logically.

2. Build a Security Diagnostic Page

Create a hidden page with:

• A table of SecurityUserTenant filtered by current user.
• A table of SecurityUserOrg filtered by current user.
• Cards for USERPRINCIPALNAME(), USERNAME(), TenantKey.

Example measure:

Current User = USERPRINCIPALNAME()

Use this page when debugging with View as or in the service (with test accounts).

3. Keep Security Tables Small and Indexed

Performance issues often come from:

• Large SecurityUserTenant tables with no indexing upstream.
• Complex RLS expressions doing string operations on large columns.

Mitigate by:

• Ensuring UserPrincipalName is indexed in the source system.
• Avoiding unnecessary transformations in RLS expressions.

Step 8: Governance and Maintenance Practices

RLS is not a one-time setup. Treat it like code.

Version and Document Your Security Model

At minimum, keep:

• A diagram of security tables and relationships.
• A short doc explaining:
  • What each role does.
  • Which tables are RLS-filtered.
  • How tenants and org units are mapped.

Automate User-Tenant Assignments

Manual updates to SecurityUserTenant won’t scale. Options:

• Sync from HR / CRM / IAM systems.
• Use scheduled ETL (e.g. SQL, Fabric pipelines) to rebuild security tables.

Align with Workspace and App Permissions

RLS controls what rows a user sees, not which reports they can access.

• Use workspace roles / app permissions to control access to content.
• Use RLS to control data visibility within that content.

A user should never gain access to a report via RLS; they must already have access via workspace/app permissions.

One Practical Takeaway

Design your RLS around data-driven security tables, not around hard-coded roles. If you can map users to tenants, regions, and access levels in tables and keep your RLS expressions as simple “filter by current user” rules, you’ll get a model that is easier to maintain, safer to extend, and ready for multi-tenant scenarios without rewrites.