What is Row Level Security (RLS)
Row level security is a feature in Power BI that allows you to restrict data access on who is viewing the report. Instead of creating multiple dashboards for separate audiences, RLS allows you to apply filter dynamically so each user can only see the data that is relevant to them. This is accomplished by defining roles and applying DAX filters to limit data access for each role. Power BI will then check what role a user is assigned to when they open the report and automatically filter out any data they do not have access to.
Load User Access Table
The first step is to load in a separate table that maps usernames to the desired field that you want to restrict. For this example, I have three users, each acting as the head of their respective departments in a retail store. The goal is to configure Power BI, so that each department leader can only view data relevant to their assigned area and nothing else.
| Username | Category |
|---|---|
| john@example.com | Furniture |
| jane@example.com | Technology |
| jane@example.com | Office Supplies |
Create a Relationship
Now that the user access table has been loaded into Power BI. The next step is to create a One-to-Many relationship between the user and main table.
- On the left side pane, select Model View
- Drag and drop the Category field from UserAccess onto
- Select the two columns (Category and Category)
- Set the Cardinality to Many to one (*:1)
- Leave Cross-filter direction to single
- Save
Create a New Security Role
Now that we have created a relationship between the two tables. We want to create the security roles.
- Select Modeling
- Within Security, select Manage Roles
- Create a new Role
- Rename the role (e.g. CategoryAccess)
- Select the table that contains your user information
- Switch to DAX editor
- Input the following DAX ‘Accounts[Username] = USERPRINCIPALNAME()‘
Test the Views
USERPRINCIPLENAME() is a function in Power BI that returns the login name of the user currently viewing the report. Therefore, by using this function in your RLS setup, users are only able to see the data related to their assigned category. Once all roles are setup, it is important to test that your security rules are working as expected. To do this, Power BI Desktop has a built in “View As” feature.
- Go to the Modeling Tab
- Select ‘View As’
- Check ‘Other User’ and your created role (CategoryAccess)
- Input one of your users emails
- Select Okay
You should now only be able to see data related to the user’s current department. For this example, Jack as the head of Furniture, can only see Furniture related data. After testing access for each user I’ve confirmed that all roles are working as expected, completing the row level security setup.
