Author: Subakaran C
Row level security (RLS) in PowerBI is a security feature that restricts the access to dataset 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, to whom the dashboards are shared.
PowerBI Desktop is an application that can be installed in our local desktop which 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 is a cloud based, software as a service (SaaS) which can be used for gateway connections, manage RLS and share the dashboards.
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.
Steps to create Static RLS
This can be used when we have a small number of users.
Select Manage roles from the Modeling tab in the PowerBI desktop.
Click on Create and provide the name of the role.
Select the column from the table where RLS needs to be applied.
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 in 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 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 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 on it.
Steps to create Dynamic RLS
This can be used when we have a large number of users. Instead of creating RLS separately to each role, a table can be created and used to match the respective values.
Create a table with the column in which RLS need to be applied and 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.
Create a role in the 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 on it.
Thus the above steps can be used to create row level security with PowerBI. This can be used in applications such as user dashboard, dashboards for specific groups or branches etc.