0
0
Power BIbi_tool~15 mins

Testing RLS in Desktop in Power BI - Real Business Scenario

Choose your learning style9 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash
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 Desktop report so that sales managers only see data for their own regions.
📊 Data: You have a Sales table with columns: SalesID, Region, SalesAmount, and SalesDate. You also have a Users table with columns: UserName and Region, mapping each user to their allowed region.
🎯 Deliverable: Create a Power BI report with RLS configured. Test the RLS roles in Power BI Desktop to verify that users see only their region's sales data.
Progress0 / 6 steps
Sample Data
SalesIDRegionSalesAmountSalesDate
1North10002024-01-15
2South15002024-01-20
3East12002024-01-25
4West13002024-02-05
5North11002024-02-10
6South14002024-02-15
7East12502024-02-20
8West13502024-02-25

UserNameRegion
alice@example.comNorth
bob@example.comSouth
carol@example.comEast
dave@example.comWest
1
Step 1: Load the Sales and Users tables into Power BI Desktop.
Use 'Get Data' to import both tables from your data source or enter data manually.
Expected Result
Both tables appear in the Power BI Fields pane.
2
Step 2: Create a relationship between Users[Region] and Sales[Region].
In the Model view, drag Users.Region to Sales.Region to create a one-to-many relationship.
Expected Result
Relationship is active and shown in the model.
3
Step 3: Define a new role for RLS in Power BI Desktop.
Go to Modeling > Manage Roles > Create a role named 'RegionRole'.
Expected Result
Role 'RegionRole' is created but needs a DAX filter on Sales table.
4
Step 4: Apply a DAX filter on the Sales table for the role to restrict data to the user's region.
Use this DAX filter on Sales table: Sales[Region] = LOOKUPVALUE(Users[Region], Users[UserName], USERPRINCIPALNAME())
Expected Result
Role filters Sales data to only the region assigned to the logged-in user.
5
Step 5: Test the role in Power BI Desktop.
Go to Modeling > View as > Select 'RegionRole' and enter a test user email like 'alice@example.com'.
Expected Result
Report shows only sales data for the North region.
6
Step 6: Repeat the test for other users to verify RLS works correctly.
Test with 'bob@example.com', 'carol@example.com', and 'dave@example.com' in 'View as' mode.
Expected Result
Each test user sees only their assigned region's sales data.
Final Result
Power BI Report - Sales by Region with RLS

+----------------+----------------+
| Region         | Total Sales    |
+----------------+----------------+
| North          | 2100           |
| South          | 2900           |
| East           | 2450           |
| West           | 2650           |
+----------------+----------------+

* When testing as alice@example.com, only 'North' sales rows appear.
* When testing as bob@example.com, only 'South' sales rows appear.
✓RLS correctly restricts sales data to the user's assigned region.
✓Testing in Power BI Desktop 'View as' mode helps verify security before publishing.
✓Each sales manager sees only their region's sales, ensuring data privacy.
Bonus Challenge

Extend the RLS to allow regional managers to see their region plus all sales from the 'East' region.

Show Hint
Modify the DAX filter to include Sales[Region] = LOOKUPVALUE(Users[Region], Users[UserName], USERPRINCIPALNAME()) || Sales[Region] = "East"