How to do Row Level Security in PowerBI Desktop and PowerBI Service

This post explains the implementation of Row Level Security in PowerBI Desktop and PowerBI Service, in detail.

What is Row Level Security in PowerBI Desktop?

Row Level Security(RLS) in PowerBI can be used to restrict access to users based on their roles within the organization. Row Level Security, in a nutshell, will limit data specific to users within their organization.

Sample Example:

We are taking this sample as an example, we have the list of employees, and the various levels and divisions of employees belong to, and this information is stored in an Excel file. The PowerBI file titled ‘RLS Sample _Direct Reports test.pbix.’ Connects to the Excel file ‘Sample RLS.xlsx.’ In addition to this, there are some manual tables created in the PowerBI file that hosts the following fields:

• Employee ID
• Supervisor ID
• Employee Name
• Employee Email
• Email Path
• Employee Level
• Level1
• Level2
• Level3
• Level4

The most critical areas out of this lot are the Employee Level that will drive the Row Level Security (RLS) capabilities in PowerBI. There are two tables in the PowerBI file.

• Employee
• CurrentEmployee

The requirement here is to filter the data based on the level of the employee in the organization. For example, if an employee is assigned to Level 2, then that employee should only view data that is equivalent to Level 2 or employee data that is lower than Level 2.

Sample File:

A sample of the file titled ‘RLS Sample _Direct Reports test’ is uploaded on Git, under the directory RLS.  Please refer to the link below:

• https://github.com/nav21n/PowerBI

Steps:

1. The first step while implementing Row Level Security is to identify the security role. In this case, we have defined the role as ‘BCD Role.’ This will be the role that will filter data based on the Employee Level in the example, here. There is a sample table that’s built highlighting the Employee ID and Employee Level against the various levels added to the table.

Fig 1 – Table With The Required Fields

2.  The second step is to define the role, now that the field based on which the filter or the security role needs to be set, is determined. Select the Modeling tab -> Manage Roles. In this case,  there is a role created called as ‘BCD Role.’ To Create a              new Role, select Create a new role in the Modeling tab window.

 Fig 2 – Create New Role Under Manage Roles

3.   The third step is to select the table where the filter needs to be applied. In this case, the scenario that has been considered is that employees at Level 2 or lower shouldn’t be able to view the details of the employees at Level 1. Hence we have selected the
Employee table and defined a measure where the Employee Level is not equal to Level 1.

Fig 3 – Employee Level Expression That Defines The Filter

4.   The next step is to publish the report to the PowerBI Service, where the users will be assigned, but before doing so, we need to test the role of the data. In order to do so, there is an option to View the Role, and this will trigger the filtered data, based on the
expression defined the previous step.

Fig 4 – View, the RLS Security, defined

            Note: In PowerBI Desktop, we cannot assign users to the various roles. User assignments are available in the PowerBI Service.
After running an initial test, publish the report to PowerBI Service.

Fig 5 – Publish The Report To PowerBI Service

5.   Once the report is published to the PowerBI Service, select the DataSet that is uploaded along with the report and choose Security.

Fig 6 – Assign Security To The PowerBI DataSet

Footnotes:

1. In PowerBI Desktop, only the user roles and various expressions/measures required to implement the row level security are defined.
2. In the PowerBI Desktop, one can have a preview of the roles, based on which the data will be filtered.
3. Once the Role is defined, it is possible to assign users to those roles in the PowerBI Service, on the cloud.