The world’s leading publication for data science, AI, and ML professionals.

Sign out
Contributor Portal
Security

Explore all ways to implement RLS Rules

Implementing row-level security in Power BI is a recurring task for developers. Let's look at the techniques for implementing it.

Salvatore Cagliari
14 min read

Explore All Ways to Implement Data Security in Power BI with RLS

Implementing row-level security in Power BI is a recurring task for developers. We use various techniques to do so. Let’s look at some of them.

Photo by David Clode on Unsplash
Photo by David Clode on Unsplash

Introduction

When we regulate data access in Power BI solutions, we must implement RLS (Row-level security).

RLS works through the implementation of RLS Roles, which contain the access logic to control data access.

This logic is defined by DAX expressions and can be very simple and all the way to very complex.

As I already wrote a few articles about this topic here on Medium, I decided to collect the different methods into one guide instead of having different places where you must search for information.

In the end, I will analyze them side-by-side and recommend which approach is the best.

I will reference my other pieces if available. You can find the links to them in the References section at the end of this piece.

Variants

We have the following variants to implement RLS:

  • Simple Lookup table
  • Using Hierarchies
  • Complex DAX Expressions
  • Bonus: Using SCD2 Dimensions

How to identify the users

You can jump to the next section if you are familiar with building RLS roles.

Each RLS role uses one of the two basic approaches:

  1. Identifying the user
  2. Applying an access-logic

The first approach is based on a list of the users mapped to the data they have access to.

So, when you have a table with a list of users (in the form of Mail Addresses), you can compare the current user using the USERPRINCIPALNAME() function.

The following Measure uses this Function to show the current user:

Current User = USERPRINCIPALNAME()

Now, I can add it to a Card Visual to get the following result:

Figure 1 - Result of simple Measure for the current User (Domain hidden for Data protection) (Figure by the Author)
Figure 1 – Result of simple Measure for the current User (Domain hidden for Data protection) (Figure by the Author)

I can use this function in my RLS role(s) to check the data against the current user.

This is the basic principle of the first approach.

The other approach is to use DAX logic to implement an access logic. This logic can be simple or as complicated as needed.

You will see two examples of this approach later on.

Simple Lookup table

This is the simplest approach.

I need a list of users with a reference to the user who needs access to the data.

Each user has a reference to a subset of data within my data model.

Consider the following table with the Sales Channels:

Figure 2 - Channel table, which will be restricted by a lookup table (Figure by the Author)
Figure 2 – Channel table, which will be restricted by a lookup table (Figure by the Author)

I want to limit my users’ access to one or more defined channels.

To achieve this, I need a table with the user mapping of the Channels.

Something like this:

Figure 3 - User-Mapping for the Channels (Figure by the Author)
Figure 3 – User-Mapping for the Channels (Figure by the Author)

I import this table into my Power BI file and add a Relationship to the Channel table.

But, in this case, I must change the Default Relationship between the two tables to this:

Figure 4- Relationship between the Channel and the Channel-Accesslist tables (Figure by the Author)
Figure 4- Relationship between the Channel and the Channel-Accesslist tables (Figure by the Author)

These settings are necessary because Power BI would create a Many-to-One relationship, where the Channel table (on the one side) would filter the Channel-Accesslist table (on the Many side).

Therefore, I must change the Relationship settings to ensure that the Filter moves from the Channel-Accesslist to the Channel table and applies the security filter to it.

Next, I must create an RLS role for the Channel-Accesslist table:

Figure 5 - Create an RLS-Role for the Channel-Accesslist (Figure by the Author)
Figure 5 – Create an RLS-Role for the Channel-Accesslist (Figure by the Author)

Don’t forget to click the Save button to save the role addition before closing the dialog.

This DAX expression must return either TRUE or FALSE, regardless of complexity. Returning a resultset or a value is not permitted.

To test the Access for John Doe, I can click on the "View as" button, select the RLS role I want to test and enter a Mail Address (which is the Principalusername) to apply the selected role to the given user:

Figure 6 - Test the RLS role for John Doe (Figure by the Author)
Figure 6 – Test the RLS role for John Doe (Figure by the Author)

When I test the Access, I will get this for John Doe:

Figure 7 - Result of RLS test for John Doe (Figure by the Author)
Figure 7 – Result of RLS test for John Doe (Figure by the Author)

This is the expected result.

Using Hierarchies

Using hierarchies with RLS is a slightly different story.

Let’s look at this example:

Figure 8 - Example with product hierarchy (Figure by the Author)
Figure 8 – Example with product hierarchy (Figure by the Author)

Now, let’s assume we have Salespeople responsible for single Product Categories. Therefore, they must see only the Subcategories and the Products assigned to them.

To set the permissions, we leverage Power BI possibilities to filter one column in a table and Cross-filter all other columns.

Look at the following picture from the source database, which explains filter propagation for a Hierarchy (The Data is the same as in Power BI, but I renamed to columns in Power BI):

Figure 9 - Filter propagation for a hierarchy (Figure by the Author)
Figure 9 – Filter propagation for a hierarchy (Figure by the Author)

As you can see, when a Filter is applied to the Category column, it is automatically applied to the other columns, which define the hierarchy.

Therefore, we only need a filter on the Category column.

Of course, this only applies to hierarchies in this form.

If you have parent-child hierarchies, this doesn’t work anymore.

You can read this article to get a hint on how to solve this challenge:

Finding managers in organizational hierarchies

However, as Power BI doesn’t support parent-child hierarchies, we must flatten (Convert from parent-child to column-oriented) them in any case to use them meaningfully. I added a link to a helpful article showing how to do it in the References section at the end of this article.

Now, back to the flattened hierarchy.

We have two possibilities now.

  1. Create a table like in the first approach, assigning users to each Category.
  2. Create roles for each Category and assign the users to the role.

As mentioned, the first approach is the same as above.

Therefore, I will show you the second approach.

Like before, I create an RLS role. But this time, the expression directly filters the Product Category.

Figure 10 - Define RLS-Role for Product-Category "Computers" (Figure by the Author)
Figure 10 – Define RLS-Role for Product-Category "Computers" (Figure by the Author)

When I test the RLS-Role, this time without entering a User Mail-Address, I get this result:

Figure 11 - Result of testing the RLS-Role for the "Computers" Category (Figure by the Author)
Figure 11 – Result of testing the RLS-Role for the "Computers" Category (Figure by the Author)

Again, this is the expected result.

Consequently, I need to create one RLS-Role for each product category.

This allows me to segregate the user access or give one user access to multiple product categories.

However, as new Categories can be added to the data, I must add new RLS roles to cover access to them. Without a new role (Or roles), the new Category will not be visible to anyone.

Complex DAX expressions

Whenever the access rules are too complicated to implement with a classic data model, I need a more sophisticated method to control access to the data.

Here, we need complex DAX expressions.

Consider this table:

Figure 12 - Access list per user and Category and Brand (Figure by the Author)
Figure 12 – Access list per user and Category and Brand (Figure by the Author)

Each listed user must have access only to the products in the assigned combination of category and brand.

For example, John Doe has access to the Product categories "TV and Video" and "Computers" but only to the Brands "Contoso" and "Adventureworks". And Sam Sample has access only to the Products Category "Home Appliances" from "Northwind Traders, "Litware" and "Proseware", even though there are four more Brands for "Home Appliances".

As Power BI (like any other Tabular Model with Microsoft products) doesn’t allow creating Relationships between tables based on more than one column, I cannot integrate this table into the data model and use the standard method.

Therefore, I must create an RLS role using DAX.

To find the matching rows between two tables, I use the LOOKUPVALUES() function to apply an RLS role on the Product table:

NOT ISBLANK(
  LOOKUPVALUE('Accesslist by Category and Brand'[UserMailaddress]
                ,'Accesslist by Category and Brand'[Category]
                  ,'Product'[Category]
                ,'Accesslist by Category and Brand'[Brand]
                  ,'Product'[Brand]
                ,'Accesslist by Category and Brand'[UserMailaddress]
                  ,USERPRINCIPALNAME()
          )
  )

As stated above, I must return either TRUE or FALSE. Therefore, I use NOT ISBLANK() to get the needed result. When I find a matching row, I get a non-blank result, and NOT ISBLANK() returns TRUE.

When testing the RLS role with John Doe, I get the needed result:

Figure 13 - Result when applying the RLS role for the Category and the Brand to John Doe (Figure by the Author)
Figure 13 – Result when applying the RLS role for the Category and the Brand to John Doe (Figure by the Author)

As this is a very simple expression, I wanted to find another way to do it to show you the possibilities of complex expressions.

This is the DAX expression I came up with as an RLS role on the Product table to show off my DAX skills:

CONTAINS(
  -- Construct the table from the AccessList table
  CALCULATETABLE(
      SUMMARIZE('Accesslist by Category and Brand'
                ,'Accesslist by Category and Brand'[Category]
                ,'Accesslist by Category and Brand'[Brand]
                )

  -- Filter the table by the current User
  -- To get only the rows to which the User has access
    ,'Accesslist by Category and Brand'[UserMailaddress] = USERPRINCIPALNAME()
    )

  -- Compare the Rows from the AccessList table using COMBINE to the Product table
  -- TRUE is returned only when the values correspond
  ,'Accesslist by Category and Brand'[Category]
  ,'Product'[Category]
  ,'Accesslist by Category and Brand'[Brand]
  ,'Product'[Brand]
  )

This approach shows that more complex expressions are possible as RLS roles and how to construct them in Power BI.

I use the approach in my article on developing and testing RLS Rules (Link in the References section below) to get the correct approach for the Expression.

Then, I use the approach in the DAX query to find the correct solution for the RLS editor.

In my approach, I use the CONTAINS() function to compare the Access List with the Product table.

The CONTAINS() function allows me to compare multiple columns from two tables and find the matching rows.

As you can see from the Comments in the expression, I construct the table from the AccessList table while filtering it with the USERPRINCIPALNAME() for the Input of CONTAINS().

Afterward, I compare the columns one by one to find the matching rows for the current user.

However, both RLS roles have a significant effect on performance.

I observed a three times longer execution time than without the RLS role applied.

The rule is applied to each row of the Product table to determine whether the combination of Category and Brand is allowed in the result set.

OK, the DAX engine works more efficiently than explained here, but the principle is correct.

What can be a more efficient alternative?

For example, I can add calculated columns as an artificial key to be able to add a Relationship between these two tables:

Figure 14 - Composite Keys with concatenated Category and Brand (Figure by the Author)
Figure 14 – Composite Keys with concatenated Category and Brand (Figure by the Author)

I used Power Query to add a calculated column to both tables ("Product" & "Accesslist by Category and Brand"). Now I have a key column on both tables, which I can use for the Relationship:

Figure 15 - Relationship using the CompositeKey columns (Figure by the Author)
Figure 15 – Relationship using the CompositeKey columns (Figure by the Author)

Now, I can use the first approach (RLS role expression: [UserMailaddress] = USERPRINCIPALNAME()) to implement the Access Control.

The result is the same with all three approaches. But the last uses a much simpler and more efficient approach.

Anyway, the example shown here is based on the limited possibilities in the Contoso data model.

You might find much more complex situations in your data that cannot be solved with the first approach. You must develop a DAX expression to implement the rule in such cases.

Bonus: Using SCD2 Dimensions

The dimension tables will likely be historized when we access a classic Data warehouse.

Imagine that you, as a customer, are registered in a database of a company with multiple stores, which assigns you to a store based on your geographic location.

Over time, you might change your address. This might change the assignment to the geographic assignment.

In such a case, two rows exist in the Data warehouse for you:

  • One with the old Address
  • One with the new address

Each row has a validity time (Valid-From and -To).

This means we must take extra care when defining the RLS role, considering which data we must allow access to and which we must not.

This is necessary to ensure that the right salesperson can access your data for the correct validity window.

I already wrote an article on this topic, and I invite you to read it to learn more about it:

Handling historisations in Power BI

Conclusion

What I haven’t mentioned yet is that as soon as you publish the Power Bi file to the Power BI Service, you must assign the users to the RLS roles to ensure that the rules are applied to them.

Read this for more guidance:

Row-level security (RLS) with Power BI – Power BI

As you have seen, the most straightforward approach is the most efficient way to implement RLS roles.

In cases where complex rules must be applied, I try to translate them into an Access List that is as simple as possible. In an optimal case, this list resembles the example shown in the first approach.

This way, I avoid writing complicated DAX expressions in the RLS role and losing efficiency and performance.

In one of my projects, I was challenged to restrict two separate tables with the same List of permissions.

In that case, I duplicated the table with the User-/Access-List and filtered both tables with it.

Then, I added the same DAX-Expression as an RLS role to both tables, and the magic worked.

One of my colleagues once said, "I can make your Data model slow with little effort by implementing a complicated or inefficient RLS role."

This is very true, and we have to be careful not to be tempted to fall into the trap of being too ambitious and stuck to the idea of writing a cool DAX expression to show off our DAX competency. The user will not be very grateful for a slow Report.

One more note when implementing RLS roles:

SQLBI created an interesting video about limitations in DAX when RLS is in place:

It is essential to know these restrictions to avoid error messages or wrong results when writing DAX Measures.

Photo by Aaron Burden on Unsplash
Photo by Aaron Burden on Unsplash

References

Microsoft documentation on Row-level Security in Power BI:

Row-level security (RLS) with Power BI – Power BI

To transform the parent-child hierarchy into a classic hierarchy, I follow the methods described by Data Mozart:

Finding the right PATH – Understanding parent-child hierarchies in Power BI! – Data Mozart

This Article by RADACAD explains how to implement RLS with an organizational hierarchy:

Dynamic Row Level Security in Power BI with Organizational Hierarchy and Multiple Positions in…

Here are the links to all the articles I wrote about implementing RLS Rules and all other related topics.

Handling historisations in Power BI

Develop and test RLS Rules in Power BI

How to get performance data from Power BI with DAX Studio

I use the Contoso sample dataset, as I did in my previous articles. You can download the ContosoRetailDW Dataset for free from Microsoft here.

The Contoso Data can be freely used under the MIT License, as described here.

Get an email whenever Salvatore Cagliari publishes.

I make my articles accessible to everyone, even though Medium has a paywall. This allows me to earn a little for each reader, but I turn it off so you can read my pieces without cost.

You can support my work, which I do during my free time, through

https://buymeacoffee.com/salvatorecagliari

Or scan this QR Code:

Any support is greatly appreciated and helps me find more time to create more content for you.

Thank you a lot.

Written By

Salvatore Cagliari
See all from Salvatore Cagliari

Topics:

, , , ,

Share this article:

Related Articles