In this blog, I will discuss Dynamic Row-Level Security (RLS) with an organizational Hierarchy in Power BI.
This is a simple and effective implementation of dynamic RLS. Once applied, users will only be able to view their respective data.
Row-Level Security (RLS) in Power BI is a feature that restricts data visibility in reports based on user roles. It ensures that users only see the data relevant to their assigned role, allowing for secure and personalized access to report information.
Now let’s start the practical:
As shown in the screenshot below, we have two datasets: EmployeeHierarchy and Sales.
A relationship is created between them using the EmpID column, allowing us to fetch sales-related information for each employee.
Create an RLS role
The first role we will create is for admin-level users, who can see all the data in the report.
Go to Modeling > Click on Manage roles, and then click on Roles.
Double-click on the “Untitled” name and rename it to “admin”. For this role, there’s no need to select any table or write any DAX code since the admin has full access to all the data. This role is usually meant for the CEO or other senior executives in the company.
Now, create another RLS role. Double-click on the “Untitled” name and rename it to “user_role”. Next, select the “EmployeeHierarchy” table, then click on “Switch to DAX editor” under the Filter Data section, write the following DAX code.
[EmployeeEmail]= USERPRINCIPALNAME()
- USERPRINCIPALNAME() gets the logged-in user’s email.
Testing RLS in Power BI Desktop:
Let’s test the RLS in Power BI Desktop. We’ll verify it using one of the employee’s email IDs. Open the Manage Roles window, comment out the USERPRINCIPALNAME() function in the code, and replace it with any employee’s email address for testing.
[EmployeeEmail]="dinesh@sandeepraturi.onmicrosoft.com" --USERPRINCIPALNAME()
After that, click on View As, select user_role, and click OK.
As shown in the screenshot below, Dinesh can only see his own data. Similarly, you can test this for any other employee by simply changing the email ID.
Now, let’s assign roles to the users and test RLS in Power BI Service:
Publish the report to Power BI Service, then click on the three dots next to the report’s semantic model and select Security.
Now, select the user_role and add employees by their email IDs. You can search for employees by their name or email and add them. If an AD group has been created for them, you can add the AD group here as well.
As shown in the screenshot below, I have added three users: Sandeep, Dinesh, and David.
Now logged in with Dinesh’s account, and as shown in the screenshot below, he can only see his own data.
Let’s test the Admin role:
Now, assign the Admin role to a user and check whether they can see all the data or only their own data.
As an admin, the user can see all the data.
Thanks for reading this post! I hope you found it helpful. Feel free to share it with others or your teammates so they can benefit from it too. 😊
