Sales report example with Row-Level Security (RLS) .
Sales Data
Imagine you have a table called SalesData with these columns:
| SalesID | Region | SalesAmount | SalesRepEmail |
|---|---|---|---|
| 1 | North | 5000 | alice@company.com |
| 2 | South | 7000 | bob@company.com |
| 3 | East | 6000 | charlie@company.com |
| 4 | West | 8000 | diana@company.com |
| 5 | North | 4000 | alice@company.com |
| 6 | South | 9000 | bob@company.com |
Step-by-Step RLS Setup
1. Create Role
- Go to Modeling → Manage Roles → Create Role.
- Name it:
SalesRepRole.
2. Apply Dynamic Filter
Use DAX to filter rows based on the logged-in user’s email:
[SalesRepEmail] = USERPRINCIPALNAME()
This ensures each sales rep only sees their own sales records.
3. Test in Power BI Desktop
- Use View As → SalesRepRole.
- Enter a test email (e.g.,
bob@company.com). - You should only see rows where
SalesRepEmail = bob@company.com.
4. Publish to Power BI Service
- Publish the report.
- In the dataset settings → Security, assign users to the
SalesRepRole.
Example Outcome
- Alice (alice@company.com) logs in → sees only North region sales (SalesID 1 & 5).
- Bob (bob@company.com) logs in → sees only South region sales (SalesID 2 & 6).
- Charlie (charlie@company.com) → sees East region sales (SalesID 3).
- Diana (diana@company.com) → sees West region sales (SalesID 4).
Why This Matters
- Dynamic RLS scales easily across hundreds of users.
- No need to manually create separate reports for each sales rep.
- Ensures data security and personalized insights.
Let’s extend the sales report RLS example to include manager-level security, which is a common real-world .
Sales & Manager Data
SalesData Table
| SalesID | Region | SalesAmount | SalesRepEmail |
|---|---|---|---|
| 1 | North | 5000 | alice@company.com |
| 2 | South | 7000 | bob@company.com |
| 3 | East | 6000 | charlie@company.com |
| 4 | West | 8000 | diana@company.com |
| 5 | North | 4000 | alice@company.com |
| 6 | South | 9000 | bob@company.com |
ManagerMapping Table
| ManagerEmail | SalesRepEmail |
|---|---|
| manager_north@company.com | alice@company.com |
| manager_south@company.com | bob@company.com |
| manager_east@company.com | charlie@company.com |
| manager_west@company.com | diana@company.com |
Step-by-Step Manager-Level RLS
1. Create Relationship
- Relate
SalesData[SalesRepEmail]toManagerMapping[SalesRepEmail].
2. Define Role
- Go to Modeling → Manage Roles → Create Role.
- Name it:
ManagerRole.
3. Apply Dynamic Filter
Use DAX to filter based on the logged-in manager’s email:
[ManagerEmail] = USERPRINCIPALNAME()
This ensures managers only see sales records for their assigned reps.
4. Test in Power BI Desktop
- Use View As → ManagerRole.
- Enter a test email (e.g.,
manager_south@company.com). - You should see only Bob’s sales (SalesID 2 & 6).
5. Publish & Assign
- Publish to Power BI Service.
- In dataset Security, assign managers to the
ManagerRole.
Example Outcome
- manager_north@company.com → sees Alice’s sales (North region).
- manager_south@company.com → sees Bob’s sales (South region).
- manager_east@company.com → sees Charlie’s sales (East region).
- manager_west@company.com → sees Diana’s sales (West region).
Key Takeaway
- Sales reps see only their own data (via
SalesRepRole). - Managers see their team’s data (via
ManagerRole). - This layered RLS setup is scalable and interview-ready — it shows you understand both individual-level and hierarchical-level security.
Comments
Post a Comment