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.

Practice

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

easy

A. To restrict data access using fixed filter conditions

B. To dynamically change data access based on user input

C. To create interactive visualizations

D. To improve data refresh speed

Solution

1. Step 1: Understand Static RLS concept

   Static RLS uses fixed filter rules to limit data access for users.

2. Step 2: Compare with other options

   Dynamic changes or visualization improvements are not the purpose of Static RLS.

3. Final Answer:

   To restrict data access using fixed filter conditions -> Option A

4. Quick Check:

   Static RLS = fixed filters [OK]

Hint: Static RLS means fixed filters for data access [OK]

Common Mistakes:

• Confusing static with dynamic RLS
• Thinking RLS improves performance
• Mixing RLS with visualization features

2. Which of the following is the correct syntax to define a static RLS filter on a 'Region' column in Power BI?

easy

A. Region == "North"

B. FILTER(Region, Region = "North")

C. Region = "North"

D. Region IN ("North")

Solution

1. Step 1: Identify correct filter syntax for static RLS

   Static RLS uses simple filter expressions like Column = "Value".

2. Step 2: Evaluate options

   Region = "North" uses correct single equals sign and quotes. FILTER(Region, Region = "North") is a function, not direct filter. Region == "North" uses double equals which is invalid in Power BI filter. Region IN ("North") uses IN syntax which is not supported in static RLS filter expression.

3. Final Answer:

   Region = "North" -> Option C

4. Quick Check:

   Static RLS filter = Column = "Value" [OK]

Hint: Use single equals sign for static RLS filters [OK]

Common Mistakes:

• Using double equals (==) instead of single equals (=)
• Using FILTER function inside static RLS
• Using unsupported IN syntax

3. Given a static RLS rule on the 'Department' column with filter Department = "Sales", what data will a user assigned this role see?

medium

A. Rows where Department is Sales or Marketing

B. Only rows where Department is Sales

C. All rows except those where Department is Sales

D. No rows at all

Solution

1. Step 1: Understand the filter condition

   The filter Department = "Sales" restricts data to only Sales department rows.

2. Step 2: Analyze each option

   Only rows where Department is Sales matches the filter exactly. All rows except those where Department is Sales is the opposite. Rows where Department is Sales or Marketing includes Marketing which is not in the filter. No rows at all means no data, which is incorrect.

3. Final Answer:

   Only rows where Department is Sales -> Option B

4. Quick Check:

   Filter Department = Sales shows only Sales rows [OK]

Hint: Filter equals value means only that value's rows show [OK]

Common Mistakes:

• Assuming filter excludes the value instead of including
• Thinking filter includes multiple values without OR
• Confusing filter with dynamic RLS

4. You created a static RLS rule with filter Region = "East" but users assigned to this role still see all data. What is the most likely cause?

medium

A. Static RLS does not restrict data

B. The filter syntax is incorrect

C. The data model has no Region column

D. The RLS role was not assigned to the users

Solution

1. Step 1: Check role assignment

   Static RLS only restricts data if users are assigned to the role with the filter.

2. Step 2: Evaluate other causes

   Filter syntax is correct, Region column exists, and static RLS does restrict data. So the most likely cause is missing user assignment.

3. Final Answer:

   The RLS role was not assigned to the users -> Option D

4. Quick Check:

   Users must be assigned roles for RLS to apply [OK]

Hint: Assign users to roles to activate RLS filters [OK]

Common Mistakes:

• Assuming filter syntax error without checking assignments
• Thinking static RLS is ineffective
• Ignoring data model columns

5. You want to create static RLS rules to restrict sales data by region: North users see only North region data, South users see only South region data. Which approach is best?

hard

A. Create two roles with filters: Region = "North" and Region = "South", then assign users accordingly

B. Create one role with filter Region IN ("North", "South") and assign all users

C. Create no roles and rely on report filters

D. Create one role with filter Region = USERNAME()

Solution

1. Step 1: Understand requirement for static RLS by region

   Each region needs a separate fixed filter to restrict data accordingly.

2. Step 2: Evaluate options

   Create two roles with filters: Region = "North" and Region = "South", then assign users accordingly creates two roles with fixed filters and assigns users correctly. Create one role with filter Region IN ("North", "South") and assign all users allows all users to see both regions, which is incorrect. Create no roles and rely on report filters does not restrict data. Create one role with filter Region = USERNAME() uses dynamic USERNAME() which is not static RLS.

3. Final Answer:

   Create two roles with filters: Region = "North" and Region = "South", then assign users accordingly -> Option A

4. Quick Check:

   Separate roles with fixed filters for each region [OK]

Hint: Use separate roles with fixed filters for each group [OK]

Common Mistakes:

• Using one role for multiple regions
• Confusing static with dynamic RLS
• Relying on report filters instead of RLS