Dynamic row-level security (DRLS) models identify the user connected to the dataset via the USERPRINCIPALNAME() function and apply filters based on this identity. These models can use DAX functions or tables and relationships to implement a filter context specific to the given user. For example, a user and a permissions table could be added to the dataset (and hidden) so that the user table would first filter the permissions table, and the permission table would then filter the dimension to be secured, such as a Sales Territory Country.

In the following example of a permissions table, Jen Lawrence is associated with Germany, Australia, and the United States, and thus should only have visibility to these countries in any Power BI report or dashboard built on top of the dataset:

User permissions table

The other two tables in the Security Tables query group include a distinct list of User Principal Names (UPNs) and a distinct list of Sales Territory Country. The Sales Country table is necessary because the Sales Territory dimension table is more granular than the country one. The Sales Country table receives the filter context from the permissions table and uses a simple one-to-many cross-filtering relationship with the Sales Territory dimension table to filter the fact tables.

The dynamic RLS role will be defined with the User Principal Name column of the Users table equal to the USERPRINCIPALNAME() function. The relationships, and, more specifically, the cross-filtering from the Permissions table, will deliver the intended filter context for the given user. In the following screenshot from the Relationship view, a bidirectional cross-filtering relationship is defined between Sales Country Permissions and Sales Countries so that only the countries associated with the user will filter the Sales Territory dimension table:

Dynamic RLS model relationships

The Apply security filter in both directions property of the bi-directional relationship between Sales Country Permissions and Sales Countries is enabled by default. This property and the relationships-based filtering design is applicable to both import and DirectQuery datasets. The gray shading indicates that all three security tables should be hidden from the Report View.

With users or groups assigned to the dynamic security role in the Power BI Service, the role can be tested via the Test as role feature in Power BI. In the following screenshot, the user Brett is able to test the dynamic role as himself (Canada, United States), but can also view the dynamic role as though Jennifer is logged in, viewing the reports:

Testing dynamic row-level security in Power BI

In this security testing sample, the chart only displays the sales territory countries associated with Jennifer, and the three tables to the right reflect the three security tables added to the dataset. As expected, all three security tables are filtered based on Jennifer's UPN, and this filter flows through the rest of the data model via relationships among the Sales Territory dimension and all three fact tables.

It can be useful to create a dedicated security testing report that can be leveraged as security roles are created and modified. The report may contain multiple pages of visualizations representing all primary tables and any sensitive metrics or columns from across the dataset. On this project, a business analyst or a QA Tester, such as Stacy Loeb, can be mapped on to the security role and use the report to confirm that the filter context from the security role has been implemented successfully.