Dynamic row-level security implementation (part 2)

Data Power BI

In part 1 we explained the data model structure to implement dynamic row level security. In part 2 we apply the data model to a Power BI report and implement a single dynamic role to observe the row filtering.

To set up a role we navigate to the Modelling tab ribbon in Power BI and select the Manage roles option.

Power BI Home Ribbon highlighting access to Manage roles option.

Now we set up a Role called DynamicUserToEmployee, select the Manager table, and apply the following DAX expression. See the full DAX reference for this function.

[Login] = USERPRINCIPALNAME()

Image of DAX filter expression entry applied to the Manager table creating a row level security role labelled “DynamicUserToEmployee”.

The DAX function USERPRINCIPALNAME returns the username of the currently logged in user.

In Power BI Desktop it returns the name of the PC user (in domain\user format) whereas in the Power BI service it returns the name of the user logged into the browser (in email format).

The DAX expression uses the table relationships in the data model to filter employee sales data based on the logged in user. It achieves this by first filtering the Manager table and subsequently, filtering all related tables.

With no row-level security applied, the Power BI report initially presents all employee sales data with no filtering based on the report user.

Image of Power BI sales report containing table of department sales by employee with an employee filter of all employees and no row level security applied.

To test the role select the View as option under the Modelling tab in Power B then select the role you wish to test.

Image of Power BI ‘View as roles’ selection form highlighting selected row level security role “DynamicUserToEmployee”.

Let us assume Manager John Kimball is the logged in user. His view of the Power BI report will be as follows. The report filters employees linked to his user login, which in this case is Department Bikes, and it shows Bike Department Employees Kyle and Sharon only.

Image of Power BI sales report containing table of department sales by employee with employee filter and applied row level security role of “DynamicUserToEmployee”.

Please note when this report is published to the Power BI service row-level security will only be applied if the user accessing the report is assigned the Viewer role in the Power BI workspace hosting the report.

In summary, dynamic row-level security is a useful solution to restrict user access to report data where there is a large user base and data access needs to be restricted depending on the user accessing the report.