RLS is a security feature in Power BI that allows you to limit the data shown to users based on their role in your organization. It may sound complicated to set up, but it's really quite simple... Just three steps!
Note: Power BI Pro is required to use RLS features. See more details at PowerBI.com for Pro licenses.
There are essentially three steps:
Set up a table for your roles.
Define the rules.
Add the users to the Power BI service.
Step 1: Set up your Roles Table
At a bare minimum, you need to have a table with an employee identifier (ID or name), their email (company email), and any other field you want to filter on (division, department, etc.). We'll call this the RLS table. You need to use one of these columns to link to the other tables you have. For example, if you have a sales table with a column for division, you would link this column with the division column in your RLS table.
Import the RLS table into Power BI. A relationship should automatically be created between the two columns, but it never hurts to check...
Step 2: Define the RLS Rules
The next step is to enter a very simple DAX formula in the RLS window back in Power BI. Navigate to the Modeling tab, then click "Manage Roles".
Click on "Create" to begin. Give the role a meaningful name as you will need to refer to it in the next step. I'll call this one "Division RLS". Click on your RLS table and enter this formula in the Dax expression box:
[Email] = Username()
*[Email] is the name of our column. If you choose a different column name like, "Employee_Email", you would enter [Employee_Email] instead.
Alien Tip: You can test out your RLS at this stage by clicking on "View Role As". A window will appear with several options. Check the box with the role you created as well as "Other user" option. In the "Other user" section, enter in the email of any employee to test out what they will see.
Step 3: Add Users on the Power BI Service
Next, you need to save and publish your report to the service. After doing so, open your report on Powerbi.com.
We're now going to add users to this RLS group. Under Datasets, navigate to your dataset and click the ellipses for more options and click "Security".
Locate your role that you created in the previous step (it'll be whatever you named it). Here, you can type in each individual's email address. Or, if you have groups set up in your organization, you can simply add the group's email address and the RLS will use that to get to the individual's email address automatically.
Click "Add" and you're done! Now all you need to do is present your report to the company and enjoy the BI hero status!
Other Details About RLS
What does the DAX expression Username() do? It actually returns different results depending on whether the report is view on the desktop or on the service. On the desktop, it simply returns the users domain and user name like this:
On the service (Powerbi.com):
A More Complicated Situation...
Let's say you have several teams. Each team has a leader, and you want the leader to see their direct member's data, but not the other team's data. We can accomplish this by setting up a team hierarchy table. In the following table, I have a total of 6 employees split into two teams; Team A and Team B. Each has a leader:
I have 3 fields / columns set up in the above table.
Employee - this is the employee name. I'll use this to create a relationship to the sales table (though, you would normally use an ID here).
Reports To - this contains the email address of the employee that the current (row level) employee reports to, i.e. the team leader.
Level 1 Email - this is the current employee's (row level) own email address.
I created a new sales table which includes a column for the employee name which I'll use to link the Team RLS table to.
Back in Power BI, I created the relationship for these new tables...
Now to set up the RLS expression in the "Manage Roles" window. We are going to use Username() again, but with a couple more things.
[Reports To] = Username() || [Level 1 Email] = Username()
So, what's happening here? In the above DAX expression, we are using a logical OR operator (represented by the | |'s). It performs two checks at most.
First it checks if the current user is equal to any of the values in the [Reports To] field. The [Reports To] field only includes team leader email addresses. So, if the user happens to be Team Leader A, the table will filter to show all records that have TeamLeaderA@email.com in the [Reports To] field.
If the current user is not equal to any values in the [Reports To] field, it then checks if the current user is equal to any values within the [Level 1 Email] column. If it gets to this step, the current user must be a team member (i.e. not a leader) and the table will filter to a single row correspondingly.
Testing out our new RLS with Team Leader B, we can see that it's working as expected. Team Leader B can see all members of Team B, and no members of Team A.
Do you have any other experiences using RLS? Let me know in the comments!
If you want to improve your DAX skills and be even more of a BI hero, I highly recommend checking out "Supercharge Power BI" by Matt Allington.
