RLS with multiple roles in Power BI - Deep Dive

Overview - RLS with multiple roles

What is it?

Row-Level Security (RLS) with multiple roles in Power BI is a way to control what data different users can see in a report. It lets you create different roles with specific filters on your data, and users are assigned to one or more of these roles. When a user views the report, they only see the data allowed by their roles. This helps keep sensitive information safe and relevant to each user.

Why it matters

Without RLS, everyone who opens a report sees all the data, which can lead to privacy issues or confusion. Multiple roles allow more flexible and precise control, so users only see what they need. This improves security, compliance, and user experience by tailoring data views to different job functions or departments.

Where it fits

Before learning RLS with multiple roles, you should understand basic Power BI report building and simple RLS with a single role. After mastering this, you can explore dynamic RLS using DAX expressions and integrating RLS with Power BI service user management.

Mental Model

Core Idea

RLS with multiple roles filters data dynamically based on all the roles a user belongs to, showing only the combined allowed data.

Think of it like...

Imagine a library where each visitor has one or more library cards granting access to certain sections. The visitor can only enter sections allowed by any of their cards, so their access is the combination of all their cards.

┌───────────────┐
│   User Logs In│
└──────┬────────┘
       │
       ▼
┌───────────────┐
│Check User Roles│
│(Role A, Role B)│
└──────┬────────┘
       │
       ▼
┌─────────────────────────────┐
│Apply Filters from All Roles  │
│(Role A Filter OR Role B Filter)│
└──────┬──────────────────────┘
       │
       ▼
┌───────────────┐
│Show Filtered  │
│Data to User   │
└───────────────┘

Build-Up - 6 Steps

1

Foundation

Understanding Basic Row-Level Security

Concept: Learn what RLS is and how it restricts data for users in Power BI.

Row-Level Security (RLS) lets you create rules that filter data based on user identity. For example, a sales manager only sees sales from their region. You define a role with a filter on the data table, then assign users to that role in Power BI service.

Result

Users assigned to the role see only the filtered data when they open the report.

Understanding basic RLS is essential because it forms the foundation for controlling data visibility in reports.

2

Foundation

Creating Roles with Filters in Power BI Desktop

Concept: Learn how to create roles and apply filters inside Power BI Desktop.

In Power BI Desktop, go to Modeling > Manage Roles. Create a new role and define a DAX filter expression on a table, like [Region] = "West". This role filters data to only show rows where Region is West. You can create multiple roles this way.

Result

Roles with filters are saved in the report and can be tested using 'View as Roles'.

Knowing how to create roles with filters lets you prepare your report for secure data access before publishing.

3

Intermediate

Assigning Multiple Roles to Users

🤔Before reading on: do you think a user assigned to multiple roles sees data filtered by all roles combined with AND or OR logic? Commit to your answer.

Concept: Understand how Power BI combines filters when a user belongs to multiple roles.

When a user has multiple roles, Power BI combines the filters using OR logic. This means the user sees data allowed by any of their roles, not just the intersection. For example, if Role A filters Region = West and Role B filters Region = East, a user in both roles sees data from both regions.

Result

Users with multiple roles see the union of all role filters, expanding their data access.

Knowing that multiple roles combine filters with OR helps you design roles carefully to avoid unintended data exposure.

4

Intermediate

Testing Multiple Roles in Power BI Desktop

🤔Before reading on: do you think 'View as Roles' lets you test multiple roles simultaneously or only one at a time? Commit to your answer.

Concept: Learn how to simulate multiple roles in Power BI Desktop to verify RLS behavior.

Power BI Desktop's 'View as Roles' feature lets you select one or more roles to simulate. By selecting multiple roles, you can see the combined filtered data as a user with those roles would see it. This helps validate your RLS setup before publishing.

Result

You can preview exactly what data a multi-role user will see in the report.

Testing multiple roles together prevents surprises and ensures your security rules work as intended.

5

Advanced

Using DAX for Complex Multi-Role Filters

🤔Before reading on: do you think you can write a single DAX filter that handles multiple roles dynamically? Commit to your answer.

Concept: Explore writing DAX expressions that adapt filters based on multiple roles dynamically.

Instead of static filters per role, you can write DAX expressions using USERNAME() or USERPRINCIPALNAME() to check user membership dynamically. For example, a DAX filter can check if the current user belongs to a list of allowed users or roles and filter data accordingly. This allows more flexible multi-role scenarios.

Result

Dynamic DAX filters enable fine-grained control over data visibility beyond fixed role filters.

Understanding dynamic DAX filtering unlocks powerful, scalable RLS setups for complex organizations.

6

Expert

Handling Conflicts and Performance in Multi-Role RLS

🤔Before reading on: do you think having many overlapping roles improves or harms report performance? Commit to your answer.

Concept: Learn about challenges with overlapping roles and how to optimize RLS for performance and correctness.

When multiple roles have overlapping filters, Power BI combines them with OR logic, which can increase the data scanned and slow down queries. Conflicts may arise if roles have contradictory filters. To optimize, design roles with minimal overlap, use dynamic DAX filters carefully, and test performance with large datasets.

Result

Proper role design avoids slow reports and unintended data leaks in multi-role RLS setups.

Knowing the performance impact and conflict risks helps you build efficient, secure multi-role RLS models.

Under the Hood

Power BI applies RLS filters at the data query level before data reaches the report visuals. When a user logs in, Power BI checks all roles assigned to them and combines their filters using OR logic. This combined filter is translated into a query predicate that limits rows returned from the data source or in-memory model. The filtering happens transparently and dynamically for each user session.

Why designed this way?

Combining filters with OR allows users with multiple roles to see all data they are permitted to access, avoiding overly restrictive views. This design balances security with usability. Alternatives like AND logic would restrict data too much, causing confusion. The system also supports dynamic DAX filters for flexibility. The tradeoff is complexity in managing overlapping roles and potential performance costs.

┌───────────────┐
│User Logs In   │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│Identify Roles │
│(Role 1, 2, 3) │
└──────┬────────┘
       │
       ▼
┌─────────────────────────────┐
│Combine Filters with OR Logic │
│(Filter1 OR Filter2 OR Filter3)│
└──────┬──────────────────────┘
       │
       ▼
┌───────────────┐
│Apply Filter to│
│Data Query     │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│Return Filtered│
│Data to Report │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: If a user belongs to two roles with conflicting filters, do they see no data or combined data? Commit to your answer.

Common Belief: If roles have conflicting filters, the user will see no data because the filters cancel out.

Reality: Power BI combines role filters with OR logic, so the user sees data allowed by either role, not the intersection.

Why it matters: Believing filters combine with AND can lead to overly restrictive roles and confusion when users see more data than expected.

Quick: Can you assign a user to multiple roles in Power BI service? Commit to your answer.

Common Belief: Users can only be assigned to one role at a time in Power BI service.

Reality: Users can be assigned to multiple roles, and Power BI combines their filters accordingly.

Why it matters: Not knowing this limits your ability to design flexible security models that reflect real-world user responsibilities.

Quick: Does Power BI Desktop enforce RLS filters when you publish a report? Commit to your answer.

Common Belief: RLS filters are always active in Power BI Desktop after creating roles.

Reality: RLS filters only apply when you use 'View as Roles' in Desktop or when the report is published and accessed by users with assigned roles.

Why it matters: Misunderstanding this can cause confusion during development and testing of RLS.

Quick: Does adding many roles improve report performance by narrowing data? Commit to your answer.

Common Belief: More roles always improve performance by filtering data more precisely.

Reality: Multiple roles combine filters with OR, which can increase data scanned and reduce performance if roles overlap heavily.

Why it matters: Ignoring this can cause slow reports and poor user experience in production.

Expert Zone

1

Role filters combine with OR logic, but within a single role, multiple table filters combine with AND logic, which can create subtle data access patterns.

2

Dynamic RLS using DAX can simulate multiple roles in one filter expression, reducing complexity but requiring careful maintenance.

3

Power BI caches RLS filter results per user session, so changes in role assignments may require users to refresh or re-login to see updated data.

When NOT to use

Avoid multi-role RLS when your security needs require strict intersection (AND) of filters; instead, consider dynamic RLS with custom DAX or separate reports. Also, if performance is critical and roles overlap heavily, consider simplifying roles or using data marts with pre-filtered data.

Production Patterns

In real-world systems, multi-role RLS is used to model complex organizational structures like matrix teams or contractors with multiple access levels. Experts often combine static roles with dynamic DAX filters for user attributes, and rigorously test with 'View as Roles' and Power BI service to ensure security and performance.

Connections

Access Control Lists (ACLs)

Similar pattern of granting permissions based on multiple roles or groups.

Understanding RLS multi-role logic helps grasp how ACLs combine permissions from multiple groups in IT security.

Set Theory in Mathematics

RLS multi-role filters combine sets of allowed data using union (OR) operations.

Knowing set union clarifies why data from all roles is combined rather than intersected.

Library Book Borrowing Systems

Users have multiple library cards granting access to different book sections, similar to multiple roles granting data access.

This real-world system shows how multiple permissions combine to expand access, mirroring RLS multi-role behavior.

Common Pitfalls

#1 Assigning overlapping roles without understanding OR logic causes unintended data exposure.

Wrong approach:

Role A filter: [Region] = "North"
Role B filter: [Region] = "South"
User assigned to both roles expecting to see only "North" data.

Correct approach:

Design roles carefully or combine filters in one role if intersection is needed, e.g., [Region] = "North" AND [Department] = "Sales".

Root cause:

Misunderstanding that multiple roles combine filters with OR, not AND.

#2 Testing RLS only with single roles leads to missing multi-role access issues.

Wrong approach:

Using 'View as Roles' and selecting only one role at a time during testing.

Correct approach:

Use 'View as Roles' selecting multiple roles simultaneously to simulate real user access.

Root cause:

Not realizing users can have multiple roles and filters combine accordingly.

#3 Writing static filters for each role without dynamic user checks causes maintenance overhead.

Wrong approach:

Creating many roles with hardcoded user lists instead of dynamic DAX filters.

Correct approach:

Use DAX functions like USERPRINCIPALNAME() to create dynamic filters that adapt to user identity.

Root cause:

Lack of knowledge about dynamic RLS capabilities in Power BI.

Key Takeaways

Row-Level Security with multiple roles in Power BI combines filters using OR logic, showing users data allowed by any of their roles.

Users can belong to multiple roles, so testing with multiple roles selected is essential to verify correct data access.

Dynamic DAX filters enable flexible and scalable multi-role security beyond static role filters.

Poorly designed overlapping roles can cause unintended data exposure and performance issues.

Understanding how Power BI applies and combines RLS filters helps build secure, efficient, and user-friendly reports.