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.

Practice

1. What is the primary purpose of Row-Level Security (RLS) in Power BI Service?

easy

A. To change the report layout based on user preferences

B. To improve report loading speed by caching data

C. To create new data columns dynamically in reports

D. To restrict data access so users see only data they are allowed to view

Solution

1. Step 1: Understand RLS purpose

   RLS is designed to limit data visibility for different users based on roles.

2. Step 2: Identify correct purpose in options

   Only To restrict data access so users see only data they are allowed to view describes restricting data access to authorized users.

3. Final Answer:

   To restrict data access so users see only data they are allowed to view -> Option D

4. Quick Check:

   RLS controls data visibility = D [OK]

Hint: RLS controls who sees what data in reports [OK]

Common Mistakes:

• Confusing RLS with report performance features
• Thinking RLS changes report design
• Assuming RLS creates new data columns

2. Which step must be done in Power BI Service after defining roles in Power BI Desktop to enforce RLS?

easy

A. Assign users or groups to the defined roles

B. Create new calculated columns for roles

C. Publish the report again without changes

D. Change the data source credentials

Solution

1. Step 1: Recall RLS workflow

   After defining roles in Desktop, you must assign users to those roles in Service.

2. Step 2: Match correct action in options

   Only Assign users or groups to the defined roles describes assigning users to roles, which activates RLS.

3. Final Answer:

   Assign users or groups to the defined roles -> Option A

4. Quick Check:

   Assign users to roles in Service = A [OK]

Hint: Assign users to roles in Service to activate RLS [OK]

Common Mistakes:

• Thinking you must create columns for RLS in Service
• Assuming republishing alone enforces RLS
• Confusing data source credentials with RLS setup

3. Given a role defined with filter [Region] = "West", what data will a user assigned to this role see in the report?

medium

A. Data from all regions without filtering

B. All data except Region West

C. Only data where Region is West

D. No data will be visible

Solution

1. Step 1: Understand role filter meaning

   The filter [Region] = "West" limits data to rows where Region equals West.

2. Step 2: Determine visible data for user

   User assigned to this role sees only data matching the filter, i.e., Region = West.

3. Final Answer:

   Only data where Region is West -> Option C

4. Quick Check:

   Role filter = visible data = C [OK]

Hint: Role filters show only matching data rows [OK]

Common Mistakes:

• Assuming filter excludes matching data
• Thinking user sees all data regardless of filter
• Believing filter hides all data

4. You assigned users to roles in Power BI Service but they still see all data. What is the most likely issue?

medium

A. Users were assigned to roles correctly

B. Roles were not published from Power BI Desktop

C. Data source credentials are invalid

D. Report visuals are not refreshed

Solution

1. Step 1: Check role publishing status

   Roles must be defined and published from Desktop to Service for RLS to work.

2. Step 2: Identify why users see all data

   If roles are not published, assignments in Service have no effect, so users see all data.

3. Final Answer:

   Roles were not published from Power BI Desktop -> Option B

4. Quick Check:

   Roles must be published for RLS to apply = A [OK]

Hint: Publish roles from Desktop before assigning users [OK]

Common Mistakes:

• Assuming assigning users alone activates RLS
• Blaming data source credentials for RLS issues
• Forgetting to refresh report visuals

5. You have two roles: SalesWest with filter [Region] = "West" and SalesEast with filter [Region] = "East". A user belongs to both roles. What data will they see in the report?

hard

A. Data where Region is West or East

B. Data where Region is both West and East simultaneously

C. Only data from the first assigned role

D. No data because roles conflict

Solution

1. Step 1: Understand multiple role behavior

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

2. Step 2: Apply OR logic to role filters

   User sees data where Region = West OR Region = East, so both regions' data are visible.

3. Final Answer:

   Data where Region is West or East -> Option A

4. Quick Check:

   Multiple roles combine filters with OR = B [OK]

Hint: Multiple roles combine filters with OR logic [OK]

Common Mistakes:

• Thinking filters combine with AND logic
• Assuming only one role applies
• Believing conflicting roles block all data