Dynamic Row Level Security in Power BI

| Analytics , Power BI ,

Data security is a critical aspect in any business intelligence tool. Companies have to make sure that their data is secure (both internally and externally). One way to secure data is by using dynamic row-level security wherein the data access is restricted only to a particular user or a group of users – for eg; if you want the sales manager of a particular branch to be able to access/view data specific only to his branch, you can use dynamic row level security. This blog explains how we can apply dynamic row level security (RLS) in a Power BI report.

Step 1: Open Power BI Desktop and go to File > Get Data. Choose a data source (I have chosen Excel) and upload the data. There are hundreds of different data sources you connect to in Power BI.

datasource-powerbi

Step 2: Once you select the data source (Excel), you will get a pop-up screen to select the file to be uploaded.

datasource-powerbi

Step 3: Once you select the file, click on Open. Another pop – up screen appears listing all the Tables on left and preview of the tables on the right. Select the desired table and click on either “load” or “transform data”. Load option will directly load the data and Transform Data allows you to model your data.

transform-data-powerbi

I have selected “Transform data” and a screen as shown below appears. It contains the sales and user group Table.

transform-data-powerbi

Step 4: I have made couple of changes to the data. Once done, select “Close & Apply” to save the changes made.

power-query-editor

Step 5: We need a table which contains unique values of the group which will act as a bridge between the User Group and Sales Data Table. Since my Sales table has duplicate values, I’m creating a new table.

To create a new table go to Menu > Table Tools > New Table and add the Dax Code as below

Group = VALUES(Sales[Group])

new-table-powerbi

Step 6: Now select the ✔ Icon and save the table.

new-table-powerbi

Step 7: Go-to Model Tab and create a relationship between these three tables. Right click on the table and select “Manage Relationships

relationships-powerbi

Once you select “Manage Relationships”, a pop-up screen will appear.

manage-relationships-powerbi

Step 8: Select “New”. It opens up a pop-up screen with the relationship details. Now select the related Tables from the drop-downs.

sales-group-powerbi

Step 9: Once the Tables are selected, select the column which is common for both the tables. It creates a relationship between them as seen below. Click “OK”.

create-relationship-powerbi

Step 10: Connect the Group table with the Sales Table as a one-many relationship. Create a relationship with user group table to group it as a many-one relationship. Set the Cross Filter Direction drop-down as both and enable the “Apply security filter in both direction “ as shown below.

edit-relationship-powerbi

The Group Table will act as bridge between the User group and Sales Table as shown below.

group-table-powerbi

Step 11: Go to the Modeling tab and select Manage Roles.

manage-roles-powerbi

Step 12: Create a new role, provide a name for the role. Under Tables, select the table to which you want to apply a DAX rule. In the Table filter DAX expression box, enter the DAX expression as shown below:

Users[Users] = USERPRINCIPALNAME()

manage-roles-powerbi

After you’ve created the DAX expression, select the check mark above the expression box to validate the expression and click on Save.

Step 13: Once you’ve created your roles, you can test the results of the roles. Select View as Roles > Other User and enter the user’s email address and click OK.

view-as-roles-powerbi

Step 14: Now save the application and publish it to the workspace. Go to Workspace > Datasets and select and choose the security.

dynamic-rls-powerbi

You can see the roles as shown below. Add the list of users who can access the report.

As seen below, data is restricted to select users.

       Note: While using Power Embedded BI, you need not add the users in the Roles.

Note: In order to view the dynamic row level security in a report, make sure the user is a “viewer” in your workspace. Only viewers added under Roles can view the Dashboard

Dynamic RLS approach is based on email address of the user accessing the report and it limits the dataset to only that particular user/group of users. This approach greatly reduces the number of Power BI reports and security roles that we need to create and also simplifies the management of the groups assigned to security roles once the report is published to Power BI.

  Limitations:

  • If you have already defined roles and rules in Power BI service, you have to re-create them in Power BI Desktop.
  • You can define row level security only on the datasets created within Power BI Desktop. If you want to enable row level security for datasets created with Excel, you must convert your files into Power BI Desktop (.pbix) files first.
  • Only Import and DirectQuery connections are supported. Live connections to Analysis Services are handled in the on-premise model.

Share with: