Static RLS rules in Power BI - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants you to create a sales report that only shows data for the 'East' region to certain users. You need to apply static Row-Level Security (RLS) rules to restrict data access.

📊 Data: You have a sales dataset with columns: OrderID, Region, SalesAmount, and OrderDate.

🎯 Deliverable: Create a Power BI report with a table visual showing sales data, and apply a static RLS rule so users assigned to the 'EastRegion' role only see sales from the East region.

Sample Data

OrderID	Region	SalesAmount	OrderDate
1001	East	500	2024-01-15
1002	West	700	2024-01-16
1003	East	300	2024-01-17
1004	North	400	2024-01-18
1005	South	600	2024-01-19
1006	East	200	2024-01-20
1007	West	800	2024-01-21
1008	South	450	2024-01-22

1

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

Use 'Get Data' to import the sales table with columns OrderID, Region, SalesAmount, OrderDate.

Expected Result

Sales data is loaded and visible in the Fields pane.

2

Step 2: Create a table visual to display sales data.

Add a Table visual with fields: OrderID, Region, SalesAmount, OrderDate.

Expected Result

Table visual shows all sales records from all regions.

3

Step 3: Define a static RLS role named 'EastRegion' to filter data for the East region only.

In Power BI Desktop, go to Modeling > Manage Roles. Create a role 'EastRegion' with the DAX filter: [Region] = "East".

Expected Result

Role 'EastRegion' is created with filter restricting data to East region.

4

Step 4: Test the RLS role to verify it filters data correctly.

Use 'View as Roles' and select 'EastRegion'.

Expected Result

Table visual shows only rows where Region is East (OrderID 1001, 1003, 1006).

5

Step 5: Publish the report to Power BI Service and assign users to the 'EastRegion' role.

In Power BI Service, assign users to the 'EastRegion' role under dataset security settings.

Expected Result

Users in 'EastRegion' role see only East region sales data when viewing the report.

Final Result

Sales Report Table
+---------+--------+------------+------------+
| OrderID | Region | SalesAmount| OrderDate  |
+---------+--------+------------+------------+
| 1001    | East   | 500        | 2024-01-15 |
| 1003    | East   | 300        | 2024-01-17 |
| 1006    | East   | 200        | 2024-01-20 |
+---------+--------+------------+------------+

✓Static RLS restricts data so users only see sales from the East region.

✓Users assigned to the 'EastRegion' role cannot see sales from other regions.

✓This improves data security and ensures users see only relevant data.

Bonus Challenge

Create dynamic RLS rules that filter data based on the logged-in user's region instead of a static region.

Show Hint

Use a user mapping table with usernames and regions, then write a DAX filter using USERNAME() function.