Dynamic RLS eliminates the need to create and maintain individual roles for every user by leveraging the logged-in user's identity. Here are three key takeaways from this video:
- Dynamic RLS uses the user's login email to determine data access automatically. Instead of hard-coding filter rules for each region or department, a single role uses the USERPRINCIPALNAME function to match the logged-in user's email against a lookup table connected to your data model.
- The data model does the heavy lifting. By connecting a user-territory lookup table to the rest of your data through relationships, filter flow determines what each user can see. The role itself contains no hard-coded filters — it simply verifies the user's identity.
- There are always two steps: desktop and service. Setting up the dynamic role in Power BI Desktop defines the filtering logic, but you must also grant users access to the semantic model in the Power BI Service. Without service-level access, users cannot view the report at all, even if the role is configured correctly.
This lesson is a preview from our Power BI Certification Course Online (includes software & exam). Enroll in this course for detailed lessons, live instructor support, and project-based training.
This is a lesson preview only. For the full lesson, purchase the course here.
In static row-level security, each role contains explicit, hard-coded filter rules. A "France" role says: show data where region equals France. A "UK" role says: show data where region equals United Kingdom. As the number of regions, departments, or territories grows, so does the number of roles you need to create and maintain.
Dynamic RLS takes a fundamentally different approach. Instead of building filter logic into individual roles, it uses the logged-in user's email address to look up their data access permissions from a table within the data model itself. A single role handles all users, and what each person sees is determined by the data, not by the role definition. This makes dynamic RLS significantly more scalable and easier to manage, especially in organizations with many users across many segments.
Setting Up the Data Model
The foundation of dynamic RLS is a lookup table that maps each user's email address to the data they should be able to access. In a territory-based example, this table contains columns for the salesperson's name, their email address, and their assigned sales territory key.
This table must be connected to the rest of the data model through a relationship. By linking the sales territory key in the lookup table to the sales territory key in the territories table, you establish a filter flow path. When the system identifies which row in the lookup table belongs to the current user, that row's territory key filters through the territories table and down into the sales data, restricting everything the user can see.
The relationship and filter direction are critical. Filters must be able to flow from the lookup table through territories and into sales. This is the mechanism that makes dynamic RLS work: it is the data model's structure, not hard-coded role logic, that determines what each user sees.
Creating the Dynamic Role
The dynamic role is created in Power BI Desktop under the Modeling tab, just like a static role. The difference is in the rule. Instead of writing a condition like "region equals France," you write a DAX expression that compares the email column in the lookup table to the USERPRINCIPALNAME function. This function returns the email address of the currently logged-in user.
When the expression evaluates, it finds the row in the lookup table where the email matches the current user's login, and that row's relationships to the rest of the model determine which data is visible. You can test this directly in Power BI Desktop using the "View As" feature by specifying different email addresses to simulate different users.
Deploying to the Power BI Service
As with static RLS, creating the role in Power BI Desktop only defines the filtering logic. You must also publish the semantic model to the Power BI Service and grant users access to the model separately. This is a two-part process: the desktop role controls what a user can see, while the service controls whether they can access the dataset at all.
If a user has not been granted access to the semantic model in the service, they will be unable to view any report based on it, regardless of whether a dynamic role exists for their email. Once access is granted, the service checks the dynamic RLS role, finds the user's email in the lookup table, and applies the corresponding filter.
Scaling and Reuse
One of the greatest advantages of dynamic RLS is scalability. Adding a new user requires only adding a row to the lookup table with their email and territory assignment, then granting them access to the semantic model in the service. No new roles need to be created or modified.
For larger organizations, users can be added to the service using security groups rather than individually, making management even more efficient. And because the security is applied at the semantic model level, all reports built on that model automatically inherit the same access controls. You secure the data once, and every report connected to it benefits from that security.
