RLS with multiple roles in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

DAX Filter Behavior with Multiple Roles

Consider a Power BI model with a table Sales and a table Users. The Users table has a column Role with values 'Manager' and 'SalesRep'. You apply Row-Level Security (RLS) with two roles: Manager role filters Region = "North", and SalesRep role filters SalesPerson = USERNAME(). If a user belongs to both roles, what will be the effective filter on the Sales table?

A The filters combine with AND, so only sales where Region = 'North' AND SalesPerson = USERNAME() are visible.

B Only the filter from the first assigned role applies, ignoring the second role.

C The filters combine with OR, so sales where Region = 'North' OR SalesPerson = USERNAME() are visible.

D No filters apply because multiple roles conflict and cancel each other.

💡 Hint

Think about how Power BI combines filters when a user is in multiple roles.

In Power BI, when a user belongs to multiple roles, the filters from each role combine with a logical OR. This means the user sees data that matches any of the role filters.

🧠 Conceptual

intermediate

Understanding RLS Role Membership

In Power BI, how does the system determine which roles apply to a user when multiple roles are defined?

A A user can only be assigned to one role at a time, chosen by the administrator.

B Roles are ignored if a user belongs to more than one; only the default role applies.

C The user must manually select which role to use when opening the report.

D A user can belong to multiple roles simultaneously, and all their filters apply combined with OR.

💡 Hint

Think about how Power BI handles multiple roles for a single user.

Power BI allows users to belong to multiple roles at once. The filters from all roles are combined with OR to determine the data visible to the user.

🔧 Formula Fix

advanced

Troubleshooting Unexpected RLS Behavior

A report uses two RLS roles: RegionRole filters Region = "East", and DeptRole filters Department = "Sales". A user belongs to both roles but sees no data. What is the most likely cause?

A Power BI does not support multiple roles; only one role filter applies, and it is misconfigured.

B The user’s roles filters combine with OR, but the data model has no rows matching either filter.

C The user’s roles filters combine with AND, so only rows where Region = 'East' AND Department = 'Sales' are shown, but no such rows exist.

D The user’s roles filters combine with XOR, causing no data to be shown.

💡 Hint

Consider how the filters combine and the data in the model.

Power BI combines RLS role filters with OR when a user belongs to multiple roles. Therefore, the user should see rows where Region = 'East' OR Department = 'Sales'. If no data is visible, the most likely cause is that the data model has no rows matching either condition.

❓ visualization

advanced

Designing a Dashboard to Show Role-Based Data

You want to create a Power BI dashboard that shows sales data filtered by RLS roles: Manager sees all regions, SalesRep sees only their own sales. Which visualization design best helps users understand their filtered data?

A A bar chart with a dynamic title showing the user’s role and a slicer for region, disabled for SalesRep role.

B A pie chart showing sales by region, but with no indication of role-based filtering.

C A table listing all sales data with no filters, so users can see all data regardless of role.

D A single bar chart showing total sales without any labels or filters, relying on RLS only.

💡 Hint

Think about how to communicate role-based filtering clearly to users.

Option A uses a dynamic title to show the user’s role and disables slicers for SalesRep to prevent confusion. This helps users understand what data they see and why.

🎯 Scenario

expert

Implementing Complex RLS with Multiple Roles and Hierarchies

Your company has a hierarchy: Country > Region > Store. You have three RLS roles: CountryManager filters by Country, RegionManager filters by Region, and StoreManager filters by Store. A user belongs to both RegionManager and StoreManager roles. How should you implement RLS so the user sees all data for their region plus their specific store data, without losing any data?

A Create separate roles for each level and rely on Power BI’s OR filter combination to show union of region and store data.

B Combine all filters into a single role using AND logic to restrict data to both region and store simultaneously.

C Assign the user only to the most restrictive role to avoid filter conflicts.

D Use DAX USERPRINCIPALNAME() in a single role to filter only the store data, ignoring region.

💡 Hint

Think about how multiple roles combine filters and how to show union of data.

Power BI combines filters from multiple roles with OR, so creating separate roles for each level and assigning the user to both roles allows the user to see all data for their region plus their store data.

Practice

1. What does RLS with multiple roles in Power BI allow you to do?

easy

A. Control data access by assigning users to one or more roles

B. Create multiple dashboards for different users

C. Automatically refresh data at different times

D. Change the data source connection dynamically

Solution

1. Step 1: Understand RLS purpose

   RLS (Row-Level Security) controls which data a user can see based on roles.

2. Step 2: Recognize multiple roles effect

   Assigning multiple roles means a user sees data allowed by any of those roles (combined access).

3. Final Answer:

   Control data access by assigning users to one or more roles -> Option A

4. Quick Check:

   RLS multiple roles = combined data access [OK]

Hint: RLS controls data visibility by roles combined with OR logic [OK]

Common Mistakes:

• Confusing RLS with dashboard creation
• Thinking RLS changes data refresh
• Believing RLS changes data source

2. Which of the following is the correct way to define a role filter in Power BI Desktop for multiple roles?

easy

A. Assign users directly in Power BI Desktop without roles

B. Use DAX filter expressions for each role separately under 'Manage Roles'

C. Write SQL queries inside Power BI Desktop to filter roles

D. Create a single role and assign all users to it

Solution

1. Step 1: Understand role creation in Power BI Desktop

   Roles are created by defining DAX filter expressions under 'Manage Roles'.

2. Step 2: Recognize multiple roles setup

   Each role has its own filter; multiple roles mean multiple filters defined separately.

3. Final Answer:

   Use DAX filter expressions for each role separately under 'Manage Roles' -> Option B

4. Quick Check:

   Role filters use DAX expressions in 'Manage Roles' [OK]

Hint: Define each role filter with DAX in 'Manage Roles' [OK]

Common Mistakes:

• Trying to use SQL queries inside Power BI Desktop
• Assigning users directly without roles
• Using only one role for all users

3. Given two roles in Power BI: RoleA filters 'Region' = "East" and RoleB filters 'Sales' > 1000. If a user is assigned both roles, what data will they see?

medium

A. Data where Sales > 1000 but Region can be any value

B. Only data where Region = "East" AND Sales > 1000

C. Data where Region = "East" but Sales can be any value

D. Data where Region = "East" OR Sales > 1000

Solution

1. Step 1: Understand multiple roles filter logic

   When a user has multiple roles, Power BI combines filters with OR logic, not AND.

2. Step 2: Apply OR logic to given roles

   User sees data where Region = "East" OR Sales > 1000, meaning either condition allows data visibility.

3. Final Answer:

   Data where Region = "East" OR Sales > 1000 -> Option D

4. Quick Check:

   Multiple roles combine filters with OR [OK]

Hint: Multiple roles combine filters with OR, not AND [OK]

Common Mistakes:

• Assuming filters combine with AND logic
• Confusing role assignment with user permissions
• Thinking roles restrict data more strictly

4. You created two roles with filters but users assigned to both roles see all data. What is the most likely cause?

medium

A. Users were not assigned to roles in Power BI Service

B. Roles were created but not saved in Power BI Desktop

C. Data source connection is broken

D. Filters use AND logic instead of OR, causing no matching data

Solution

1. Step 1: Check user assignment to roles

   Even if roles exist, users must be assigned in Power BI Service to see filtered data.

2. Step 2: Understand effect of missing assignment

   If users are not assigned, RLS filters do not apply, resulting in all data visible.

3. Final Answer:

   Users were not assigned to roles in Power BI Service -> Option A

4. Quick Check:

   User assignment in service is required for RLS [OK]

Hint: Assign users to roles in Power BI Service to enable RLS [OK]

Common Mistakes:

• Assuming filters combine with AND logic
• Ignoring user assignment step
• Blaming data source connection first

5. You have three roles: Role1 filters 'Country' = "USA", Role2 filters 'Product' = "A", Role3 filters 'Year' = 2023. A user is assigned Role1 and Role3. Which DAX expression correctly represents the combined filter effect for this user?

hard

A. FILTER(Table, Table[Country] = "USA" && Table[Product] = "A" && Table[Year] = 2023)

B. FILTER(Table, Table[Country] = "USA" && Table[Year] = 2023)

C. FILTER(Table, Table[Country] = "USA" || Table[Year] = 2023)

D. FILTER(Table, Table[Product] = "A" || Table[Year] = 2023)

Solution

1. Step 1: Understand role filter combination logic

   When a user has multiple roles, Power BI combines filters with OR logic, so data matching any role filter is visible.

2. Step 2: Apply OR logic to Role1 and Role3 filters

   The combined filter is where Country = "USA" OR Year = 2023, ignoring Role2 since user is not assigned to it.

3. Final Answer:

   FILTER(Table, Table[Country] = "USA" || Table[Year] = 2023) -> Option C

4. Quick Check:

   Multiple roles combine filters with OR [OK]

Hint: Combine multiple role filters with OR in DAX [OK]

Common Mistakes:

• Using AND instead of OR to combine filters
• Including filters from roles not assigned to user
• Confusing role filters with user permissions