Implementing Static & Dynamic RLS

This lesson walks through the step-by-step process of implementing both static and dynamic Row-Level Security.

Implementing Static RLS

Scenario: Create roles for East and West regions.

Step 1: Define Roles in Power BI Desktop

1. Go to Modeling → Manage Roles
2. Click Create → Name: "East Region"
3. Select the Sales table
4. Enter DAX filter: [Region] = "East"
5. Click ✅ → Create another role: "West Region"
6. DAX filter: [Region] = "West"
7. Click Save

Step 2: Test Roles in Desktop

1. Go to Modeling → View as Roles
2. Select "East Region" → Click OK
3. Verify: only East region data is visible
4. Click Stop viewing to exit

Step 3: Publish & Assign Users

1. Publish the report to Power BI Service
2. Navigate to the dataset → ••• → Security
3. Select "East Region" → Add users (e.g., alice@company.com)
4. Select "West Region" → Add users (e.g., bob@company.com)
5. Click Save

---

Implementing Dynamic RLS

Scenario: Sales representatives should see only their own orders.

Data Structure:

Step 1: Define Role in Desktop

1. Modeling → Manage Roles
2. Create role: "Sales Rep"
3. Select Sales table
4. DAX filter: [SalesRepEmail] = USERPRINCIPALNAME()
5. Save

Step 2: Test in Desktop

1. Modeling → View as Roles
2. Check both "Sales Rep" role AND "Other user"
3. Enter a test email (e.g., alice@company.com)
4. Verify: only Alice's orders are visible

Step 3: Publish & Assign

1. Publish to Power BI Service
2. Dataset → Security → "Sales Rep" role
3. Add all sales reps to this single role
4. Each user automatically sees only their own data

---

Dynamic RLS with a Security Mapping Table

For complex scenarios, use a separate security table.

Security Table (UserAccess):

Relationship: UserAccess[Region] → Sales[Region] (Many-to-One)

Role DAX Filter (on UserAccess table):

[UserEmail] = USERPRINCIPALNAME()

How It Works:

1. User alice@company.com logs in
2. RLS filters UserAccess to Alice's rows (East, North)
3. The relationship propagates the filter to Sales table
4. Alice sees data for both East AND North regions

Benefits: • One role handles all users • Users can have access to multiple regions • Add/remove access by editing the UserAccess table (no DAX changes)

---

Dynamic RLS with Hierarchy (Manager Access)

Scenario: Managers should see their own data PLUS their team's data.

Employee Table:

DAX Filter:

[EmployeeEmail] = USERPRINCIPALNAME() ||
[ManagerEmail] = USERPRINCIPALNAME()

Result: • Alice sees only her own data • Manager sees Alice's, Bob's, and their own data • Director sees everyone's data

Best Practices

• Prefer Dynamic RLS for scalability • Use security mapping tables for flexible access control • Always test with "View as Roles" before publishing • Use Azure AD security groups for efficient user management • Keep DAX filters simple for performance • Document your RLS design for future maintenance