Row-Level Security (RLS) in Power BI is a powerful feature that enables report developer to restrict data access at the row level based on the identity of the report user or assigned role group. Instead of creating separate reports for each audience, security roles are defined with filter expressions that automatically filter data based on the current report viewer.
Why RLS Matters and How Does it Work?
Without RLS, every user with access to a report sees the exact same dataset. For organizations with hierarchical structures, such as distinct sales regions, departments, managers, and individual contributors, RLS ensures each person sees only the information relevant to their position.
For example, within a single Power BI report:
- A sales rep sees only their own territory.
- A regional manager sees the entire region’s data.
- An executive sees national or global figures.
RLS operates by applying strict, hidden filters to the data model. Developers write DAX (Data Analysis Expressions) to filter the dimension tables, which in turn filter the related fact tables. These expressions are linked to a predefined role in Power BI Desktop. Finally, once the report is published to the Power BI Service, report consumers are simply assigned to those created RLS roles.
Static vs. Dynamic RLS
Power BI supports two approaches to row-level security:
Static RLS assigns users to roles manually, and all users in a role see the same filtered data. For example, you create a “Finance” role that filters to a specific set of regions, and then assign specific users to that role. The filter never changes based on who is viewing it.
Dynamic RLS automatically filters data based on the report user’s identity using DAX functions like USERNAME() or USERPRINCIPALNAME(). The filter adapts to whoever is viewing the report, eliminating the need to manually assign users to roles. A salesperson named “Alice” automatically sees only her accounts; a manager sees all accounts in their team.
Configuring a Static RLS in Power BI Desktop
Step 1: Open the Role Management
In Power BI Desktop, go to the Modeling tab and click Manage roles.
Step 2: Create a Role
Click Create and give your role a descriptive name like SalesTerritory, Lagos Island, or Finance.
Step 3: Define Filter Expressions
Select a table you want to restrict, then write a DAX expression that returns TRUE for rows that should be visible. For example, to restrict a DimBranch table:
Testing a Role
It is important to test defined RLS role after the creation to verify that it has the intended effect.
- Go to the Modeling tab in the Report view and select View as
Now select a defined role to test
Then confirm the role work as intended in a visual
Configuring Dynamic RLS in Power BI Desktop
While static RLS requires you to build and maintain a separate role for every branch, region or department, dynamic RLS allows you to create just one role that automatically adapts to the user viewing the report. It does this by reading the viewer’s login credentials.
Prerequisites to Creating Dynamic RLS:
1. Prepare a RLS Support Table:
To use dynamic RLS, Power BI needs a reference guide of who is allowed to see what. Create or import a security table into your data model that maps user email addresses to their respective data categories (for example, a table containing UserEmail and RegionID columns).
2. Establish the Relationship:
Go to the Model view and create a relationship between the new RLS Support table and the relevant table (e.g., DimBranch or FactSales).
Note: Depending on your schema, you may need to edit the relationship properties and check the box for Apply security filter in both directions so the filter successfully flows down to your fact table.
3. Create a Single Dynamic Role:
Click Create and name your role something encompassing, like DynamicSecurity. Select the RLS Support table and write a DAX expression that compares the email column to the currently logged-in user:
The USERPRINCIPALNAME() function automatically captures the email address of the person viewing the report and filters the mapping table to only their assigned rows.
4.Test the Dynamic Role:Modeling tab.
Testing a dynamic role requires one extra step compared to static roles, as you need to simulate a specific user’s login.
Click View as in the Report view. Check the box for your newly created dynamic role. Then, check the Other user box and type in an email address from your mapping table. Click OK and verify that the visuals filter to show only that specific user’s data.
The Dynamic RLS automatically filters Quzeem to Ibadan
Publishing and Managing RLS in the Power BI Service
After publishing your .pbix file to the Power BI service, you manage role assignments in the workspace:
- Go to the dataset Settings > Security.
- Add members to each role by email, group, or application.
For dynamic RLS, the Power BI service automatically evaluates the USERNAME() or USERPRINCIPALNAME() against your organizational directory (Azure Active Directory) when the report is viewed.
Select the desired role and enter a user, user group or security group
Choosing Between Static and Dynamic RLS
Deciding which method to implement depends entirely on the size of your organization, the complexity of your data, and how frequently user roles change.
Static RLS Static RLS is best suited for simple scenarios where access rules are straightforward and user roles remain relatively stable over time.
- Pros:
- Easier and faster to set up for basic scenarios.
- Relies on simple, rule-based filters applied uniformly to everyone assigned to the role, making it highly predictable.
- Cons:
- Lacks the flexibility to accommodate complex access rules.
- Cumbersome to scale. If you have 50 different sales regions, you have to create and manage 50 separate roles.
- Requires manual administration in the Power BI Service to update role memberships whenever an employee changes positions or leaves the company.
Dynamic RLS Dynamic RLS is the preferred approach for complex environments, larger organizations, or any scenario where access rules need to update automatically.
- Pros:
- Easily handles complex and granular access requirements.
- Highly scalable, requiring only one role to manage access for thousands of users.
- By sourcing your security mapping table directly from a central system (like an HR database or Active Directory), the solution automatically adapts to changes in user roles and permissions without manual intervention.
- Cons:
- Requires more upfront effort, data modeling, and DAX knowledge to establish.
- The added complexity can make troubleshooting access issues more challenging if a user isn’t seeing the correct data.
In summary, static RLS is suitable for simple, stationary scenarios, while dynamic RLS is the better investment for complex environments that require automated, scalable security.
However, regardless of which method you implement, remember one golden rule: RLS is not a replacement for workspace-level permissions. Row-Level Security controls what data a user can see once they are already inside a report. If a user should not have access to a report at all, you must manage that strictly through workspace roles and dataset permissions, not RLS.
