• Home
  • Services

      Business Intelligence

      Automation & Applications

      Business Advisory

  • Knowledge Hub
  • FAQ
  • About Us
  • Home
  • Services

      Business Intelligence

      Automation & Applications

      Business Advisory

  • Knowledge Hub
  • FAQ
  • About Us
Sign In
Get in touch

Streamlining your tech stack for maximum efficiency

Learn, explore, and grow with our knowledge hub.

Implement Row-Level Security in Power BI the Right Way | Smart Statistics
Power BI Security Tutorial

Implement Row-Level Security in Power BI the Right Way

Sharing one Power BI report across several regions, departments or customer teams can quickly become a security problem if every user can see every row.

Row-level security solves that problem by applying data filters according to the identity and role of the report consumer.

This practical tutorial shows how to build a scalable dynamic RLS pattern using a user-access table, model relationships and USERPRINCIPALNAME() , then test the result properly before deployment.

Discuss Your Power BI Security → Explore Our Services
One report Serve several audiences from one model.
Dynamic access Filter data using the signed-in identity.
Central control Maintain access in a governed mapping table.
Testable security Validate roles before users rely on them.
Security belongs in the model Do not rely on hidden pages, filters or duplicated reports as a substitute for genuine data security.
Why Row-Level Security Matters

Keep the Same Report, Change the Data Each User Can See

RLS is useful when people should consume the same report structure but only see the rows relevant to their role, region, customer portfolio, department or business unit.

Reduce Unnecessary Data Exposure

Users see the subset of rows permitted by the security model rather than the complete dataset.

  • Regional reporting
  • Department reporting
  • Account-manager portfolios
  • Customer-facing analytics

Avoid Duplicated Reports

A well-designed RLS model can remove the need to maintain separate report copies for every audience.

  • One semantic model
  • One measure set
  • One visual design
  • Different permitted rows

Centralise Access Logic

Dynamic RLS can use a maintained mapping table so access changes do not require a new role for every user.

  • User-to-region mapping
  • User-to-team mapping
  • Many users per region
  • Many regions per user

Support Repeatable Testing

Roles can be tested against representative identities before the report is released to the wider business.

  • Desktop role testing
  • Service role testing
  • Expected-row checks
  • Access regression tests
Choose the Right Pattern

Static RLS vs Dynamic RLS

Both approaches have valid use cases, but dynamic RLS is usually easier to scale when access rules differ by user and change over time.

Simple environments

Static RLS

Static RLS creates explicit roles with fixed filter rules. A user is then assigned to the appropriate role.

  • Easy to understand for a small number of roles
  • Suitable for stable, broad access groups
  • Can become difficult to maintain at scale
  • Role proliferation can create administration overhead
Scalable pattern

Dynamic RLS

Dynamic RLS identifies the signed-in user and uses a mapping table to determine which security keys that identity may access.

  • One role can support many users
  • Users can be mapped to one or several regions
  • Access can be maintained as data
  • Works well with a governed access-management process
Model Design

Build Security into the Semantic Model

The dynamic pattern becomes much easier to reason about when the security mapping, business dimension and fact table have clear relationships.

Example access path

In this example, a user's permitted region keys flow through the model to filter the sales fact table.

UserAccess UserPrincipalName
RegionKey
→
DimRegion RegionKey
RegionName
Area
Country
→
FactSales DateKey
RegionKey
CustomerKey
SalesAmount

Why this pattern scales

The role only needs to identify the current user. The mapping table decides which region keys belong to that identity, and normal relationships carry the filter into the report data.

Design principle: keep the security key stable, separate access mapping from descriptive business attributes, and test the relationship path deliberately.

If one user needs access to several regions, store several rows for that user in the access table rather than creating special-case DAX for that individual.

Step-by-Step Tutorial

Implement Dynamic RLS from Start to Finish

The example below secures regional sales data, but the same pattern can be adapted to departments, account portfolios, legal entities and other business dimensions.

01

Define the access requirement before writing DAX

Write down the business rule in plain English. For example: regional managers should see only regions assigned to them, while the national sales manager should see all assigned regions through the same mapping structure.

  • Who are the report consumers?
  • What business key controls access?
  • Can one user have several permitted keys?
  • Who owns access changes?
Tip: define access using stable business keys rather than display labels that may be renamed later.
02

Create the user-access mapping table

Build a table with one row for every permitted user-and-security-key combination.

A user with three permitted regions should therefore have three rows. 

UserPrincipalName              RegionKey
-----------------------------  ---------
manager.north@example.com      R001
manager.north@example.com      R002
manager.scotland@example.com   R005
national.sales@example.com     R001
national.sales@example.com     R002
national.sales@example.com     R005
Production note: the values above are illustrative identities and keys. Use your organisation's governed identity and access data rather than typing live security rules into the report manually.
03

Create the relationships that carry the security filter

Relate the access table to the secured dimension using the business key, then relate that dimension to the fact table through the normal model design.

  • UserAccess[RegionKey] → DimRegion[RegionKey]
  • DimRegion[RegionKey] → FactSales[RegionKey]
  • Keep the relationship path simple and intentional
  • Avoid adding bidirectional security filtering without a clear reason
RLS filters propagate through active relationships. If the report still shows too many rows, inspect the relationship path before adding more DAX.
04

Create one dynamic RLS role

In Power BI Desktop, create a role and apply the identity filter to the UserAccess table. 

UserAccess[UserPrincipalName] = USERPRINCIPALNAME()

The role now filters the access table to the rows for the signed-in user.

The permitted region keys then flow through the model relationships.

Why this is preferable to user-specific roles: the DAX remains stable as users join, leave or change territory. Access changes are handled by maintaining the mapping data.
05

Test with View as in Power BI Desktop

Use the role-testing feature in Power BI Desktop and enter representative user identities where appropriate.

Confirm that each test identity sees exactly the expected regions and no others.

  • Test a single-region user
  • Test a multi-region user
  • Test a user who should see no rows
  • Test totals, drill-through and tooltips
Do not stop at checking one chart. Test totals, detail pages and any report feature that queries the secured semantic model.
06

Publish the semantic model and assign role members

Publish the model and report, then manage security on the semantic model in the Power BI service.

Assign the intended users or approved groups to the dynamic role.

  • Prefer governed groups where appropriate
  • Keep role membership separate from report design
  • Document who can change role membership
  • Review access when teams or responsibilities change
07

Test the published role in the Power BI service

Use the service's Test as role capability to verify that the published model behaves as expected with the assigned role and identity logic.

  • Confirm expected regions are visible
  • Confirm prohibited regions are absent
  • Confirm total values change correctly
  • Confirm the production report uses the intended semantic model
08

Check workspace permissions carefully

This is one of the most important deployment checks.

Power BI RLS is enforced for users with Viewer permissions.

It does not restrict workspace Admins, Members or Contributors because those roles have edit-level access to the content.

Critical control: do not give ordinary RLS-secured report consumers Member or Contributor access to the workspace and expect RLS to protect their view. Use appropriate Viewer/app distribution patterns instead.
09

Document and monitor the access process

Treat the security mapping as a controlled business process.

Define who adds users, who approves access, how leavers are removed and how exceptions are reviewed.

  • Named access owner
  • Documented approval route
  • Regular access review
  • Repeatable test cases
  • Clear response to failed or unexpected access
Security Testing

Test More Than the Happy Path

Security testing should include permitted users, users with multiple access keys and identities that should not return any data at all.

Single-region user

Confirm that every visual, total and detail page is restricted to one permitted region.

Positive test

Multi-region user

Confirm that the user receives the union of all mapped regions without leaking unrelated rows.

Mapping test

Unmapped user

Confirm that an identity without an authorised mapping does not unexpectedly receive broad access.

Negative test

Drill-through page

Validate drill-through and detail views, not just the landing page of the report.

Feature test

Export scenario

Confirm exported data follows the same permitted view expected for the report consumer.

Output test

Permission change

Change a mapping deliberately and confirm the updated access is reflected after the relevant refresh process.

Change test
Deployment Control

RLS and Workspace Roles: Know the Boundary

The semantic-model role is only part of the security design. Workspace permissions determine whether RLS is actually enforced for a user consuming the content.

Viewer

RLS is enforced for users consuming content with Viewer permissions.

Contributor

Contributors have edit-level workspace access, so RLS does not restrict them in the same way as Viewers.

Member

Members can collaborate on workspace content and are not protected by RLS as ordinary report consumers.

Admin

Administrators control the workspace and are not restricted by semantic-model RLS.

Common RLS Mistakes

Avoid These Security Pitfalls

Many RLS issues come from model design or workspace permissions rather than from the DAX expression itself.

Problem Why It Matters Better Approach
Creating a role for every individual Role administration becomes difficult as the user population grows or territories change. Use a dynamic user-access table where the business rule permits it.
Securing by labels instead of keys Renamed regions or inconsistent spelling can break access logic unexpectedly. Use stable surrogate or business keys for the security relationship.
Assuming hidden pages are secure Hiding report elements changes presentation, not row-level data access. Enforce data security in the semantic model.
Giving consumers Contributor access RLS is not enforced for workspace Contributors. Use Viewer/app distribution for ordinary secured consumers.
Testing only one user Single-user testing can miss multi-key, unmapped or exception scenarios. Maintain a small regression test matrix.
Using unnecessary bidirectional security filters Complex filter paths can make security logic harder to understand and troubleshoot. Keep the model directional and simple unless the security requirement genuinely needs more.
Frequently Asked Questions

Power BI Row-Level Security FAQs

What exactly does Power BI row-level security restrict?
RLS filters rows in the semantic model according to the role rules. It is designed to restrict data rows, not to hide entire model objects such as particular tables, columns or measures.
What is the difference between static and dynamic RLS?
Static RLS uses fixed role filters. Dynamic RLS uses the current user's identity together with access data to determine which rows are permitted.
Why use USERPRINCIPALNAME()?
USERPRINCIPALNAME() returns the current user's UPN, which can be matched to a user-access mapping table to implement a dynamic security pattern.
Can one user have access to several regions?
Yes. Store one row per permitted user-and-region pair in the access table. The role filters the user's rows, and all matching region keys can then propagate through the model.
Does RLS apply to workspace Members and Contributors?
No. Power BI documentation states that RLS is enforced for Viewer permissions and does not apply to workspace Admin, Member or Contributor roles.
Should I use security groups for role membership?
Microsoft guidance recommends using security groups where practical because group membership can be managed centrally rather than maintaining many individual role assignments in the semantic model.
Is RLS enough to hide sensitive columns?
RLS is row-level security. If the requirement is to restrict access to model objects such as columns or measures, review object-level security and wider model permissions rather than expecting RLS to provide that behaviour.
How can Smart Statistics help?
Smart Statistics can review Power BI semantic models, design dynamic RLS, validate workspace permissions, create security test cases and improve wider reporting governance for UK businesses.

Technical references

This tutorial is aligned with current Microsoft guidance for Power BI row-level security, workspace roles and RLS modelling practices.

Always check current tenant-specific behaviour, licensing and product documentation before a production rollout.

Need Confidence That the Right People See the Right Data?

Smart Statistics helps UK businesses design Power BI solutions that are not only useful and attractive, but also governed, maintainable and secure by design.

We can review your semantic model, implement dynamic RLS, strengthen workspace permissions, improve data modelling and help establish repeatable security testing.

Book a Consultation → Explore Smart Statistics Services