Marcin Kapuściński | 22 March 2022

RLS: a row-level security in Power BI

Row-level security (RLS), in the context of Power BI, refers to limiting the rows of data that a specific user can see when viewing a report. This allows you to better control what users can see in the published report based on their Power BI login account.

For example, using this technique, a regional manager can view data for his region but not data for other regional managers. Thanks to this approach to data protection, it is possible to publish a single report or dashboard (we manage and maintain only one report then) instead of several identical ones supplied with a different set of data.

There are different methods of using RLS with the Power BI service. You can configure row-level security as plain, so-called. static RLS or dynamic RLS.

Static RLS means that we define security logic, in the form of roles assigned to a data filtering expression in the Power BI file (.pbix), and for each logic change we have to open the PBIX file, apply the change, save the file and republish it.

Only users (preferably domain groups) should be assigned to such a defined role on the portal – from that moment on, a user in such a role will not see any rows other than those that meet the condition of the role. However, beware – the participation of a given user in more than one role at the same time may result in the disclosure of all data! Therefore, the report and the assumed roles should be tested well.

Dynamic RLS, on the other hand, relies on a security definition created in association with the user account in the data itself. It doesn’t sound very interesting yet, so let’s focus on a specific example.

When person X logs into the system, we can read from the data tables that are loaded additionally to the model that this person should only have access to specific invoices. In the dynamic RLS table that we have created, we have an associated user login with individual invoice IDs. This method is possible in the Power BI service using the UserName () or UserPrincipalName () DAX functions. In dynamic RLS, therefore, to change the logic, it is enough to add / edit / delete records in tables. That’s all!

However, before we follow the process of creating dynamic RLS, let’s first summarize – to define dynamic RLS and security roles, you need: user table with login ID and group to which they belong, role table, appropriate relationships (user table filters the role table, and this one filters the table facts), at least one role with a DAX filter with UserName and UserPrincipalName functions.

Table of users

For dynamic RL to work, you must create a table of all users. This table must list all users with the field as their login ID for the Power BI report and the group that the user belongs to. The table can come from e.g. Active Directory or Sharepoint list.

If the report is hosted on the Power BI service, the sign-in is the email address users use to sign in to Power BI. If the report is hosted on Power BI Report Server, the login is the network account that is used to sign in to the report server.

Sample table:

Role table:

Then we move on to the problem of creating roles that can filter data based on user group. We need to create a table that holds all the associations.

Sometimes the role table is not required. The user table can act as a role table.
For example, if we are implementing a Power BI payroll report, we want each user to see only their personal data.

Relations

Now we need to link this table to both the users table and the actual fact table. It is important that the role table filters other tables in the Data Model. Filtering between tables in Power BI is defined as a relationship. We need to have a relationship with proper direction to other data tables in the role table data model.

DAX Filters

The last element is associating the current user with mail. This can be achieved by creating a role:

In the “user” table, we add the following DAX expression (username () gets the credentials of the logging in user):

After publishing the report, open the security menu of the given data set.

At this stage, we need to connect the role created in Power BI Desktop with the users of the powerbi.com service. Click Add and Save.

In summary, the user’s login credentials will filter the user table, this will in turn filter the roles table, which in turn will filter the relevant data in the fact table:

Person 1: