PowerBI How to do Row Level Security in PowerBI Desktop and PowerBI Service March 7, 20181231 views0 Share By Navneeth Nagrajan Share 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: 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: In PowerBI Desktop, only the user roles and various expressions/measures required to implement the row level security are defined. In the PowerBI Desktop, one can have a preview of the roles, based on which the data will be filtered. 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. Share What's your reaction? Excited 0 Happy 0 In Love 0 Not Sure 0 Silly 0 Navneeth NagrajanNavneeth Nagrajan is a Technology Specialist at Deloitte Australia focussing on design, development, integration, and implementation of the Microsoft Power Platform (primarily PowerBI, Common Data Service and Flow) and Dynamics 365 for Finance and Operations ERP. Other areas of focus include Azure DevOps, Github (related to Dynamics 365 for Finance and Operations deployments), and Dynamics Lifecycle Services. Profile: Twitter: http://www.twitter.com/nav21n LinkedIn: https://www.linkedin.com/in/navneeth-nagrajan-94a9aa5/ Twitter
PowerBI PowerBI DAX Reference – Link To A Complete End to End Guide By Navneeth NagrajanSeptember 5, 2018
PowerBI PowerBI Trace File FlightRecorderCurrent.trc: Where and How to Find it By Navneeth NagrajanMarch 24, 2018
PowerBI Q&A in PowerBI: Using Ask a Question feature in PowerBI Ask a Question was a feature added in the December 2017 Update (Q&A in PowerBI) ...
PowerBI Bookmarks in PowerBI: How to Set For Desktop and PowerBI Service Bookmarks in PowerBI was a capability that was introduced in the October 2017 PowerBI Feature ...
PowerBI How to Do Conditional Formatting In Power BI with Rules – Font Color Scales In PowerBI, conditional formatting was a capability that was included in the November 2017 update. ...