Ways to Configure RLS in Power BI

Level: Intermediate

Why You May Want to use RLS

Imagine you’ve just built a Power BI report filled with sensitive financial data. You want to start to share this data across the organisation but not everyone is allowed to see everything.  The CFO should be allowed to see everything, but managers should only see data related to their assigned region. The last thing you would want to do is build a different report for each person.  The good news is, there is a solution.  This is where Row-Level Security (RLS) comes into play. RLS is the solution that allows you to control who can see what in the data from a single report.  If this is what you need, then read on.

Different Approaches to Implementing RLS

There are two common ways to implement RLS; Static RLS and Dynamic RLS. Each approach is different with different strengths and weaknesses, but they have the same goal.

In this article, I will demonstrate how to use both with examples.

Static RLS

Static RLS is perfect for when you have smaller, straightforward scenarios. Often you’ll have a simple semantic model (e.g. a few columns in one or more tables in a star schema format), with clearly defined access rules that can be applied across groups of users.

Static RLS Example

Let’s say in your semantic model you have a table called, ‘Revenue’, showing Revenue data over time by Region:

As outlined earlier, there is a requirement from the CFO that each manager can only see data for their assigned region.

This example can be solved with Static RLS in a few simple steps. In this case I will show how you can create a role specific for Managers assigned to the India region.  Note, the steps below are completed in Power BI Desktop before publishing to the Power BI Service.

• Select the Modeling tab and click on Manage Roles
• Click New

• Write a simple DAX expression to filter the Revenue table where Region equals ‘India’
• Rename the role to something like ‘Manager – India’
• Click Save

Note in the DAX formula above I have used a double equal sign “==”.  This is called “strictly equal to”.  In most cases, a single equal sign will work just as well but read the link I have provided if you would like to learn more about strictly equal to.

Multiple roles can be easily created for other Regions using the same steps as above. It is just a matter of writing a different DAX expression to filter on the desired Region. There is no limit on the number of roles you create.

Testing Static RLS in Power BI Desktop

You can easily test that the Static RLS role you just created is working correctly using the following steps:

• Select the Modeling tab and click on View As
• Select the Manager – India role and click OK

With the above RLS role applied using “View as roles”, you should now only see data which have India appearing under Region, in the Revenue table:

Dynamic RLS Example

Imagine you have a scenario similar to the above, except in this case, there are hundreds of report users, each with unique data permission requirements. This is where dynamic RLS comes into play. It handles the administrative burden of having multiple report users with permissions that may change over time.

Building on the Revenue table example above, in this scenario, Managers have unique permissions on what Region they can see. This may or may not be based on their assigned Region, and they may be allowed to see data from more than one Region.

Tackling the Scenario with Dynamic RLS

Create a Permissions Table

The first step is to create a ‘Manager Permissions’ table and load it into the semantic model. This table contains a list of each Manager (using their email address) and lists which Regions they are allowed to see:

Creating Relationships

Next, create a relationship between Revenue and Manager Region based on the Region column (the common column) in both tables:

Now that the relationship is created, it’s important that the filter direction of the relationship is changed to ensure RLS works (it will not work otherwise!):

• Right click on the relationship
• Ensure the cross filter direction is set to Both
• Tick Apply security filter in both directions
• Click OK

There is an alternative approach that uses a bridging table containing all the distinct Regions to “bridge” between the Revenue and Allowed Regions tables. This is to avoid Many-to-Many relationships between the Revenue and Allowed Regions table. For simplicity purposes I will not cover it in this post.

Creating the Role

Similar to Static RLS, a role must be created to make this solution work:

• Select the Modeling tab and click on Manage Roles
• Click New
• Write the DAX expression using the USERPRINCIPALNAME() function as shown below.
• Rename the role to something like ‘Allowed Regions’
• Click Save

The USERPRINCIPLENAME() function needs some explanation.  When a user logs in to PowerBI.com, they use their email address as their username. The USERPRINCIPLELNAME() is a function that returns the username from that login.  This DAX formula therefore has the effect of checking who the logged in user is and then applying their email address as a filter on the [Manager] column.  The person logged in can therefore only see the data assigned in the manager table.

Testing Dynamic RLS in Power BI Desktop

To test what a given manager can see:

• Select the Modeling tab and click on View As
• Select the Allowed Regions role and Other user
• Enter the Managers Email Address and click OK

Note, for dynamic RLS, you must select the role and also add an email address in the other user box in order to test how it works.

In this case, I have viewed access given to [email protected] via the ‘Allowed Regions’ Role.  With this RLS in effect, you can see that only rows where Region = USA and UK are visible:

Similarly, because of the relationship between the two tables, you can then see that the Revenue table has also been filtered to only show USA and UK values for the Region column:

Some Security Advice from Me

One thing I didn’t cover in this demo is where to store the dynamic RLS table containing a list of which managers can see what data.  It is important to understand that anyone that can edit this table can also over-ride how the RLS is deployed.  It therefore follows that access to this table needs to be restricted to prevent unauthorised changes to user access.

Related Articles

This is the 1^st article of a series on RLS. In the 2^nd part I will deep dive into how to tackle a complex Dynamic RLS scenario with multiple columns in a hierarchy. Finally, I will explore how to assign users and administer RLS in the Power BI Service.