RLS with multiple roles in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a Power BI developer at a retail company.

📋 Request: Your manager wants you to create a sales report that restricts data access based on user roles: Sales Managers should see all regions, Regional Managers should see only their assigned region, and Sales Reps should see only their own sales data.

📊 Data: You have a Sales table with columns: SalesID, SalesRep, Region, Product, SalesAmount, and Date. You also have a Users table with columns: UserName, Role, and Region (Region is blank for Sales Managers).

🎯 Deliverable: Create a Power BI report with Row-Level Security (RLS) configured for multiple roles as described, so users see only the data they are allowed to access.

Sample Data

SalesID	SalesRep	Region	Product	SalesAmount	Date
1	Alice	North	Widget	500	2024-01-10
2	Bob	South	Gadget	700	2024-01-15
3	Charlie	East	Widget	300	2024-01-20
4	Alice	North	Gizmo	200	2024-02-05
5	Bob	South	Widget	400	2024-02-10
6	Charlie	East	Gadget	600	2024-02-15
7	Diana	West	Gizmo	800	2024-02-20
8	Diana	West	Widget	300	2024-03-01

UserName	Role	Region
manager1	Sales Manager
regional_north	Regional Manager	North
regional_south	Regional Manager	South
alice	Sales Rep	North
bob	Sales Rep	South
charlie	Sales Rep	East
diana	Sales Rep	West

1

Step 1: Load the Sales and Users tables into Power BI and create a relationship between Sales[Region] and Users[Region].

In Power BI Desktop, go to Model view, drag Sales[Region] to Users[Region] to create a relationship.

Expected Result

Relationship created allowing filtering Sales data by User's Region.

2

Step 2: Create a DAX calculated column in the Users table to normalize UserName to lowercase for matching with USERNAME() function.

NormalizedUser = LOWER(Users[UserName])

Expected Result

New column NormalizedUser with lowercase usernames.

3

Step 3: Create three RLS roles in Power BI Desktop: SalesManager, RegionalManager, and SalesRep.

In Modeling tab, Manage Roles, create roles with filters on Users table.

Expected Result

Roles created for RLS configuration.

4

Step 4: Define the SalesManager role with no filter to allow access to all data.

No filter

Expected Result

Sales Managers see all sales data.

5

Step 5: Define the RegionalManager role with filter to allow access only to their assigned region.

Users[Role] = "Regional Manager" && Users[NormalizedUser] = LOWER(USERNAME())

Expected Result

Regional Managers see sales data only for their region.

6

Step 6: Define the SalesRep role with filter to allow access only to their own sales data.

Users[Role] = "Sales Rep" && Users[NormalizedUser] = LOWER(USERNAME())

Expected Result

Sales Reps see only their own sales records.

7

Step 7: Test each role by using 'View as Roles' in Power BI Desktop to verify data visibility matches the role restrictions.

Use 'View as Roles' and select each role to check filtered data.

Expected Result

Sales Manager sees all data; Regional Manager sees only their region; Sales Rep sees only their sales.

8

Step 8: Publish the report to Power BI Service and assign users to the corresponding RLS roles in the dataset security settings.

In Power BI Service, go to dataset security, add users to SalesManager, RegionalManager, or SalesRep roles.

Expected Result

Users see data filtered by their assigned roles when accessing the report online.

Final Result

Power BI Sales Report Dashboard

+-----------------------------+
| Region | SalesRep | SalesAmt |
+-----------------------------+
| North  | Alice    | $700     |
| South  | Bob      | $1100    |
| East   | Charlie  | $900     |
| West   | Diana    | $1100    |
+-----------------------------+

Note: Data shown depends on logged-in user's role and region access.

✓Sales Managers can see all sales data across all regions.

✓Regional Managers see sales data only for their assigned region.

✓Sales Reps see only their own sales records.

✓RLS ensures data security and proper data access control in the report.

Bonus Challenge

Extend the RLS to allow Regional Managers to see sales data for their region plus any region where they have sales reps assigned temporarily.

Show Hint

Create a bridge table listing regions accessible per Regional Manager and update RLS filter to check membership in that list.