Dynamic Row Level Security (RLS) on Power BI

What is our Goal?

Restrict access to the user at the dataset level.

For example, If A, B and C are the Employee of XXX Company. All the employee’s data available in company data base. When A login to HR Application Then A can see only it’s related data. A cannot see other colleague data. This is the actual concept of Row Level Security.

Today we are going to implement it through POWER BI. To do that we have taken 2 Tables named ‘EmployeeHierarchy’ and ‘Fact_IncrementAmt’.

Now we have to apply security based on employee’s username in PowerBI window.

Step1: Go to Relationship window and create relation between ‘EmployeeHierarchy’ and ‘Fact_IncrementAmt’ based on EmpName column.

Step2: Go to Report window and create a table visualization using both column in ‘Fact_IncrementAmt’

Step3: Click on Modeling tab, from there click on ManageRole Options. Now click on create button to create a role and give a name of the role.

Step4: Click EmployeeHierarchy table in ManageRole window and write below DAX query into it.

[ReportsToMan] & "@sqltopossible.com" = UserPrincipalName()  ||

[Level1] & "@sqltopossible.com" =UserPrincipalName() ||

[Level2] & "@sqltopossible.com" =UserPrincipalName() ||

[Level3] & "@sqltopossible.com" =UserPrincipalName() ||

[Level4] & "@sqltopossible.com" =UserPrincipalName()

Step5: Save the entire solution and publish the Power BI report.

Note: Here I have created EmpEmail as EmpName+’@sqltopossible.com’ to make the security filter easy. First I extract username from PowerBI Login page, then compare it with ReportsToMan column with added string @sqltopossible.com.

Test case

Before publishing the report, we can test the report by giving Username in ViewasRole Window.

Let’s test the report.

Here ‘Subhdip’ is the BOSS of this table, so he can see all the Employee’s data.

Here ‘Pritam’ can’t see ‘Subhdip’ data, but he can see ‘Sourav’ and ‘Anurag’ data because of he is the manager of ‘Sourav’ and senior manager of ‘Anurag’.

Since ‘Anurag’ is lowest level employee, here he see only his data.