Author: Subakaran C
Introduction
Row-level security (RLS) in PowerBI is a security feature that restricts access to datasets for a particular role. This can be used to create personalized user dashboards for given users. The PowerBI account where the dashboards and RLS are created will have access to all the data. However, The restrictions are applied to other members with whom the dashboards are shared.
Prerequisite
PowerBI Desktop
PowerBI Service
PowerBI Desktop
PowerBI Desktop is an application that can be installed on our local desktop and can be used to transform and create calculated measures and columns from the raw dataset. The desktop application can be broadly used to create dashboards and RLS.
PowerBI Service
PowerBI service is a cloud-based software-as-a-service (SaaS) that can be used for gateway connections, managing RLS, and sharing dashboards.
Creating RLS
Consider an example for creating a dashboard for a role where the user should view only their financial details. We have records for many customers, and without applying any filters, the dashboard shows the aggregated value of all the customers.
Here RLS can be used to restrict the details to only respective users. This can be done in two ways.
Static RLS
Dynamic RLS
Steps to create Static RLS
This can be used when we have a small number of users.
Step 1:
Select Manage roles from the Modeling tab on the PowerBI desktop.
Step 2:
Click on Create and provide the name of the role.
Select the column from the table where RLS needs to be applied.
Step 3:
In the Table filter DAX expression box, enter the value that should be returned for the particular role while the RLS is applied and Save.
Step 4, Test - PowerBI desktop:
To test the RLS results on the PowerBI desktop, select View as and select the Role to be viewed and click OK.
Here we can test the dashboard and dataset restriction applied for the particular role.
Step 5, Test - PowerBI service:
To test the published dashboards in the PowerBI service, select the workspace where the dashboards are published.
Click on More options (...) and select Security.
Select the role which needs to be tested and select Test as the role.
Step 6, Applying Static RLS:
Add the valid PowerBI account email address of the person who belongs to the particular role to apply the RLS to the respective user.
Share the PowerBI dashboard with the respective user, and the user can only view the dashboards with the RLS applied to it.
Steps to create Dynamic RLS
This can be used when we have a large number of users. Instead of creating RLS separately for each role, a table can be created and used to match the respective values.
Step 1:
Create a table with the column in which RLS needs to be applied and a valid Username, and create a relationship with the existing table.
To create a relationship, click on the Model icon and select Manage relationships and click on New…
Make a relationship between the primary table and the RLS table created.
Step 2:
Create a role in a similar way as Step-1 & Step-2 of static RLS and in the Table filter DAX expression box, enter the value as userprincipalname() (userprincipalname() is a DAX function which fetches the username of the logged-in account.) and Save.
Step 3, Test - PowerBI desktop:
Click on View as and select RLS created and Other User and in the text box, enter the username to be tested.
Step 4, Applying Dynamic RLS:
Add all the PowerBI account email addresses and share the PowerBI dashboard with the respective user, and the user can only view the dashboards with the RLS applied to them.
Conclusion
Thus the above steps can be used to create row-level security with PowerBI. This can be used in applications such as user dashboards, dashboards for specific groups or branches, etc.