Row Level Security in PowerBI Desktop

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:

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.

Row Level Security in PowerBI Desktop

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.

Row Level Security in PowerBI Desktop

 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.

Row Level Security in PowerBI Desktop

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.

Row Level Security in PowerBI Desktop

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.

Row Level Security in PowerBI Desktop

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.

Row Level Security in PowerBI Desktop

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.
Disclaimer: The Questions and Answers provided on https://www.gigxp.com are for general information purposes only. We make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose.

Navneeth Nagrajan is a Technical Consultant at Avanade Australia focusing on implementation, pre-sales and support of Microsoft Dynamics 365 for Operations/ Microsoft Dynamics Ax ERP. Before Avanade, Navneeth worked as a Solutions Consultant at LM WindPower focusing on implementation and support of Microsoft Dynamics Ax Enterprise Resource Planning solution. Prior to LM WindPower, he worked as a Lead Consultant for ITC Infotech India Limited focusing on Enterprise Solutions like Microsoft Dynamics AX & Microsoft Dynamics CRM. He has worked for Microsoft as a Partner Technical Consultant. His primary focus area is Microsoft Dynamics AX and he specializes on Trade & Logistics, CRM, Service Management and Project Management functionalities. His secondary focus area is Microsoft Dynamics CRM and he specializes on Sales Management, Marketing Management and Service Management sections. He has successfully assisted more than 100 Microsoft partners and customers. At Microsoft, He has successfully written and published papers on '​'​ Needs for Microsoft Dynamics CRM in the Education Sector''. Prior to Microsoft, he has worked as a Functional Consultant and has implemented Microsoft Dynamics AX in the Manufacturing and Services Segment. He has also worked as a Business Development Executive implementing Document Management Systems for Security Broking and Manufacturing Organizations.