If you are sharing Power BI dashboards across different departments or external clients, you've likely faced a critical challenge: How do you ensure users only see the data they are authorized to see?
Creating separate reports for every manager, region, or client is a maintenance nightmare. The most efficient and professional solution is Row-Level Security (RLS).
In this guide, we will break down exactly what RLS is, the difference between Static and Dynamic RLS, and walk through a step-by-step implementation to bulletproof your Power BI reports.
What is Row-Level Security (RLS)?
Row-Level Security (RLS) is a Power BI feature that restricts data access for given users at the database row level. Instead of restricting access to the entire report, filters are applied within the data model itself.
When a user logs into the Power BI Service, RLS intercepts their credentials and filters the underlying dataset. If the VP of North America logs in, they only see North American sales. If the VP of EMEA logs in, they only see European sales—both using the exact same dashboard link.
Static RLS vs. Dynamic RLS: Which Should You Use?
Before building, it is important to choose the right approach for your semantic model.
1. Static RLS
Static RLS involves hardcoding specific filter values into roles.
- Best for: Small organizations with a few fixed categories (e.g., just three regions: East, West, Central).
- Limitation: Requires you to manually assign users to specific roles in the Power BI Service.
2. Dynamic RLS
Dynamic RLS uses DAX functions to read the logged-in user's credentials and filter the data automatically based on a security table.
- Best for: Medium to large organizations, or freelance projects where client rosters change frequently.
- Advantage: You only need to create one role and manage user access through an underlying database table.
Step-by-Step Guide: Implementing Dynamic RLS
Dynamic RLS is the industry standard for scalable reporting. Here is how to set it up.
Step 1: Create a User Security Table
Your data model needs a table that maps employee email addresses to their respective data regions or departments.
| EmployeeEmail | Region |
|---|---|
| john.doe@company.com | North America |
| jane.smith@company.com | EMEA |
Create a relationship between this Security Table and your main Fact/Dimension tables in the Power BI Model View. Ensure the cross-filter direction applies the security filter to your data.
Step 2: Define the Role in Power BI Desktop
- Open your report in Power BI Desktop.
- Go to the Modeling tab and click Manage roles.
- Click Create and name your role (e.g., "DynamicUserRole").
Step 3: Write the DAX Filter
Select your User Security Table in the Manage Roles dialog and enter the following DAX expression:
[EmployeeEmail] = USERPRINCIPALNAME()Note: USERPRINCIPALNAME() captures the exact email address the user uses to log into Power BI Service.
Step 4: Test the Role Locally
Before publishing, you must verify the DAX is working.
- In the Modeling tab, click View as.
- Check the box for your new role ("DynamicUserRole").
- Check the box for Other user and type in an email from your security table (e.g.,
john.doe@company.com).
If the visuals instantly filter to show only North America data, your RLS is perfectly configured!
Step 5: Assign Users in Power BI Service
Publish your report to the Power BI Service.
- Navigate to the Workspace containing your dataset.
- Click the three dots (More options) next to the Semantic Model (Dataset) and select Security.
- Add the email addresses (or Entra ID Security Groups) of your users to the role you created.
3 Pro-Tips for Power BI RLS
- Always use Security Groups: Instead of adding 50 individual emails in the Power BI Service, add a single Microsoft Entra ID (formerly Azure AD) security group.
- Hide the Security Table: Right-click your User Security Table in Desktop and select "Hide in report view". Users don't need to see the security logic in the fields pane.
- Test in the Service: Always use the "Test as role" feature directly in the Power BI Service dataset settings to double-check cloud execution.
Conclusion
Implementing Row-Level Security transforms a basic dashboard into an enterprise-grade, secure analytics tool. By leveraging USERPRINCIPALNAME() and Dynamic RLS, you can build a highly scalable architecture that protects sensitive data without duplicating your development efforts.
Have questions about setting up your Power BI architecture or need a custom RLS implementation? Drop a comment below or reach out directly!