In the June 2016 monthly Power BI release, Row Level Security (RLS) was introduced into Power BI desktop. This is great news for people using the application, especially as the configuration is stored within the Power BI model. Previously, you had to create the security in the web environment, which could easily be overwritten when publishing multiple times from a desktop workbook.
In this blog, I will show you how to set up RLS in Power BI desktop and how to test it works. My example uses the AdventureWorksDW2014 database (download here), specifically applying permissions for a manager. Each manager will only be able to see data for the Sales Representatives that report to them.
NOTE: This article assumes you have prior experience with creating reports and visuals in Power BI. Please leave a comment if you would like a copy of the workbook.
Report Setup
From the AdventureWorksDW2014 database, import the the FactResellerSales table. Then add another SQL dataset, pasting in the below query:
SELECT EMP.EmployeeKey
,EMP.FirstName + ‘ ‘ + EMP.LastName As SalesRepName
,EMP.Title As SalesRepTitle
,MGR.FirstName + ‘ ‘ + MGR.LastName As ManagerName
,MGR.Title As ManagerTitle
FROM [dbo].[DimEmployee] EMP
INNER JOIN
(
SELECT EmployeeKey
,FirstName
,LastName
,Title
FROM [dbo].[DimEmployee]
) MGR
ON MGR.EmployeeKey = EMP.ParentEmployeeKey
WHERE EMP.Title = ‘Sales Representative’
Create a basic bar chart and rep slicers – like shown below. You should end up with 3 managers – Amy Alberts, Stephen Jiang and Syed Abbas.
Create Roles
To create a role, navigate to Modeling tab (at the top of the page) in Power BI Desktop. You will see a section named Security. Click on Manage Roles.
Next, we need to create a role for the three managers that were previously identified. Follow the below annotation and necessary steps to create a specific role for Amy Alberts:
1. Create a Role.
2. Name the role as ‘Sales Manager Amy’.
3. Click the ‘..’ on the QueryEmployee Table.
4. Add filter (a DAX Expression).
5. Choose [ManagerName], as we want to filter on the manager’s full name. This defines what data they can see.
6. Change the “Value” filter to “Amy Alberts”. Only data containing Sales Representatives working for Amy will be shown in the report.
7. Repeat steps 1-6 for Stephen Jiang and Syed Abbas, simply replacing the “Amy Alberts” filter with the applicable person’s name. Click save to close down the RLS form. You will end up with the following:
Role Testing
Navigate to the same Modeling tab and select View As Roles.
Select ‘Sales Manager Amy’ and click OK. You will notice only Amy’s Sales Representative’s show in the report.
You can follow the same steps to impersonate a different manager or to remove the Roles filter completely.
Conclusion
The Row Level Security feature in Power BI Desktop makes it really easy to apply security around the data and what users can and cannot seen. The ability to adopt DAX filter expressions through the UI enables the user to deploy an effective security model in a simple, easy manner.
Currently, the Power BI desktop security in my example is not being used in a real life situation and the three managers can see all data in the online report. Look out for the second part of my blog on RLS (coming soon), where I will bring this to life using the Power BI Service. Tasks include tying emails to security roles/groups and actually impersonating a user to ensure they configured correctly.
Recommended Reading
o Reza Rad’s Blog – http://radacad.com/row-level-security-configuration-in-power-bi-desktop
o Power BI Community Blog – https://community.powerbi.com/t5/Community-Blog/Row-Level-Security-in-Power-BI/ba-p/31031
o Devin Knight’s Blog – https://devinknightsql.com/2016/07/01/power-bi-row-level-security/
Introduction to Data Wrangler in Microsoft Fabric
What is Data Wrangler? A key selling point of Microsoft Fabric is the Data Science
Jul
Autogen Power BI Model in Tabular Editor
In the realm of business intelligence, Power BI has emerged as a powerful tool for
Jul
Microsoft Healthcare Accelerator for Fabric
Microsoft released the Healthcare Data Solutions in Microsoft Fabric in Q1 2024. It was introduced
Jul
Unlock the Power of Colour: Make Your Power BI Reports Pop
Colour is a powerful visual tool that can enhance the appeal and readability of your
Jul
Python vs. PySpark: Navigating Data Analytics in Databricks – Part 2
Part 2: Exploring Advanced Functionalities in Databricks Welcome back to our Databricks journey! In this
May
GPT-4 with Vision vs Custom Vision in Anomaly Detection
Businesses today are generating data at an unprecedented rate. Automated processing of data is essential
May
Exploring DALL·E Capabilities
What is DALL·E? DALL·E is text-to-image generation system developed by OpenAI using deep learning methodologies.
May
Using Copilot Studio to Develop a HR Policy Bot
The next addition to Microsoft’s generative AI and large language model tools is Microsoft Copilot
Apr