Managing RLS in Service 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 set up Row-Level Security (RLS) in the Power BI Service so that sales managers only see data for their own regions.

📊 Data: You have a sales dataset with columns: SalesID, Region, SalesManager, Product, SalesAmount, and Date.

🎯 Deliverable: Create a Power BI report with RLS configured so that when published to Power BI Service, each sales manager can only view their region's sales data.

Sample Data

SalesID	Region	SalesManager	Product	SalesAmount	Date
1	North	[e-mail]	Widget	500	2024-01-05
2	South	[e-mail]	Gadget	700	2024-01-07
3	East	[e-mail]	Widget	300	2024-01-10
4	West	[e-mail]	Gadget	450	2024-01-12
5	North	[e-mail]	Gizmo	600	2024-01-15
6	South	[e-mail]	Widget	550	2024-01-18
7	East	[e-mail]	Gizmo	400	2024-01-20
8	West	[e-mail]	Widget	350	2024-01-22

1

Step 1: Load the sales data into Power BI Desktop.

Use 'Get Data' to import the sales dataset with columns: SalesID, Region, SalesManager, Product, SalesAmount, Date.

Expected Result

Sales data is loaded and visible in the Fields pane.

2

Step 2: Create a new role for RLS in Power BI Desktop.

Go to Modeling tab > Manage Roles > Create a role named 'SalesManagerRole'.

Expected Result

A new role 'SalesManagerRole' is created.

3

Step 3: Define the RLS filter for the 'SalesManagerRole' to restrict data by SalesManager.

[SalesManager] = USERPRINCIPALNAME()

Expected Result

The role filters rows where SalesManager matches the logged-in user's email.

4

Step 4: Test the role in Power BI Desktop.

Modeling tab > View as Roles > Select 'SalesManagerRole' > Enter a test user email matching a SalesManager (e.g., [e-mail]).

Expected Result

Only sales data for Alice's region is visible.

5

Step 5: Publish the report to Power BI Service.

Click 'Publish' and select the target workspace.

Expected Result

Report is published and available in Power BI Service.

6

Step 6: Assign users to the 'SalesManagerRole' in Power BI Service.

In Power BI Service, go to dataset settings > Security > Add users' emails to 'SalesManagerRole'.

Expected Result

Each sales manager's email is assigned to the role.

7

Step 7: Verify RLS works by logging in as a sales manager in Power BI Service.

Log in as Bob and open the report.

Expected Result

Bob sees only sales data for the South region.

Final Result

Dynamic by RLS

↓

Dynamic by RLS

✓Each sales manager can only see sales data for their assigned region.

✓RLS ensures data security and privacy in shared reports.

✓Testing roles in Power BI Desktop helps confirm correct filtering before publishing.

Bonus Challenge

Extend RLS to allow regional directors to see all sales data for their region, while sales managers see only their own data.

Show Hint

Create a separate role for regional directors with a filter on Region, and assign users accordingly.