How to apply RLS based on multiple columns in Power BI

Author:Murphy | View: 28971 | Time: 2025-03-22 21:35:48

Photo by Patrick Robert Doyle on Unsplash

Introduction

In previous articles, I covered much ground on Data Access in Power Bi using Row-Level Security (RLS). I explored the basics and delved into advanced techniques, and now I want to tackle RLS based on multiple columns.

In "normal" circumstances, I can control access to my data based on the data in one column.

In such cases, the RLS role can be relatively easy, as described in my previous article about RLS:

Explore all ways to implement RLS Rules

Even when we need to check multiple columns in the RLS role, we can create an RLS expression to handle this with the LOOKUPVALUE() function.

However, I want to go one step further in this piece by adding a modeling technique to simplify the DAX expression in the RLS role.

The target is how to prepare the data to reduce the RLS role to the bare minimum:

Email = USERPRINCIPALNAME()

But first, I must show you the data and the challenge with it:

Scenario and Our Data

First, let's look at my data.

My company, Evergreen Products, has a list of Employees with their respective positions within the organizational hierarchy:

Figure 1 – Extract of my Data with some managers at different levels in the hierarchy (Figure by the Author)

Look at the three marked managers. All three are responsible for a branch in the hierarchy, starting from different levels.

Andrea Adams is responsible for the entire Sales Team.
Andrea Madson is responsible for the Sales in the Central region.
John Carter is responsible for the production of large products.

Each manager must have access to the entire branch within the organization.

The challenge here is to do it efficiently.

I could author a nice DAX expression with much magic to implement this within the RLS role.

But every time a user accesses this data model, this magic but complex DAX expression is executed and can massively slow down the report.

I want to avoid this issue.

Preparing the Data model

OK, how can I solve this challenge?

I must add calculated tables and columns to map each manager to his and all subordinated organizational units in the hierarchy.

The logical target Data model is the following:

Figure 2 – Logical target Data model (Figure by the Author)

The RLS-Access table is where I will apply the DAX expression of my RLS role.

I wrote "logical target data model" as the final data model will include one more table. But more on this later.

I will create the tables with Power Query instead of DAX, as this approach will lead to more efficient data storage in the Data model.

First, I need a Key column that contains each level of the hierarchy.

For this, I will concatenate every hierarchy level into one key column. The columns for each level are separated by an underscore using the following M expression:

[Org Level 1] & "_" &
(if [Org Level 2] = null then "" else [Org Level 2]) & "_" &
(if [Org Level 3] = null then "" else [Org Level 3]) & "_" &
(if [Org Level 4] = null then "" else [Org Level 4]) & "_" &
(if [Org Level 5] = null then "" else [Org Level 5])
)

I must add the multiple "if" statements to cover the empty cells.

This is because I get the Data from a SQL Server database, and the expression is translated into SQL and sent to the source database (see Query folding).

Concatenating data with empty data (NULLs) in SQL will lead to an empty result.

Next, I will create a new table by referencing the Employee table, but only with the necessary columns:

Figure 3 – Extract of the RLS-Access table (Figure by the Author)

Then, I filter this table to include only the Managers by filtering the IsManager column to equal True.

Unfortunately, this is not enough.

I still cannot control the data access based on multiple hierarchy levels.

To solve this issue, I need a mapping table between the RLS-Access table and the Employee table, which maps every hierarchy level to every combination of roles below each level.

I do this by multiplying the RLS-Access table and appending each variant to form one large mapping table.

This mapping table is why I called the data model above "logical," as it is only a technical necessity rather than a business-relevant table.

The complete technical Data model is the following:

Figure 4 – Complete technical Data model (Figure by the Author)

Creating the mapping table

What I need to do is to create a table with one set of rows for each level.

For example, I will have one set for the entire hierarchy mapped to the top level of the CEO.

Then, I will have one set for the managers below the CEO, and so forth.

I create a new reference from the Employee table called CEOLevel in Power Query.

Next, I remove the columns "ID", "FirstName", "LastName", "Name" and "EMail".

Then, I add a new calculated column, called RLS-Key, with the following M-Expression:

[Org Level 1] & "_CEO___"

Lastly, I remove all columns except the Hierarchy Key and the new RLS-Key columns.

The table looks like this now:

Figure 5 – Extract from the table for the CEO Level (Figure by the Author)

The table contains one row for each existing organizational unit to allow the CEO to access all of them. I will explain the mechanism later.

This table is set not to be loaded into Power BI:

Figure 6 – Disabled the load for the CEOLevel table (Figure by the Author)

This is because it is an intermediary table, which will be used as a building block for the final "RLS-Mapping" table.

Now, I repeat the steps to create another intermediary table named OrgLevel2.

But the expression to create the RLS-Key column is different:

[Org Level 1] & "_" &
(if [Org Level 2] = null then "" else [Org Level 2]) & "___"

As you can see in the result, I include the second level of the Organisation with this expression:

Figure 7 – Extract of the OrgLevel2 table (Figure by the Author)

Based on these steps, I duplicate the CEOLevel table and repeat these steps to create three more intermediary tables for OrgLevel3–5.

For each table, I change the expressions for the RLS-Key column to include one more level from the hierarchy:

For OrgLevel3, I use this expression:

[Org Level 1] & "_" &
(if [Org Level 2] = null then "" else [Org Level 2])
& "_" &
(if [Org Level 3] = null then "" else [Org Level 3])
& "__"

Please note that I remove one underscore from the last line in the code each time, as it is included in the previous part of the expression.

Here for OrgLevel4:

[Org Level 1] & "_" &
(if [Org Level 2] = null then "" else [Org Level 2])
& "_" &
(if [Org Level 3] = null then "" else [Org Level 3])
& "_" &
(if [Org Level 4] = null then "" else [Org Level 4])
& "_"

And, finally, for OrgLevel5, which will not have a final underscore, as the fifth is the final level in the hierarchy:

[Org Level 1] & "_" &
(if [Org Level 2] = null then "" else [Org Level 2])
& "_" &
(if [Org Level 3] = null then "" else [Org Level 3])
& "_" &
(if [Org Level 4] = null then "" else [Org Level 4])
& "_" &
(if [Org Level 5] = null then "" else [Org Level 5])

Now, I can create the final RLS-mapping table:

I click on the CEOLevel table and click on Append Queries to create a new table:

Figure 8 – Call the "Append Queries" function to create the RLS-mapping table (Figure by the Author)

Now, I will combine all five tables into a new table:

Figure 9 – Add all intermediary tables to the new table (Figure by the Author)

The new table is called "Append1", and I rename it to RLS-Mapping.

Lastly, I will remove all Duplicates from this table by selecting both columns and executing "Remove Duplicates":

Figure 10 – Remove Duplicates from the RLS-Mapping table (Figure by the Author)

To make it all clean and tidy, I change the data types of the two columns to Text.

Now, I can integrate this table into my Data model and implement the RLS role.

Implementing RLS

After loading the new table into Power BI, I must add it to the Data model as described above.

I remove the relationship between the table "RLS-Access" and "Employees" and add two new relationships:

RLS-Mapping[RLS-Key] to RLS-Access[HierarchyKey]. Many to One
Employees[HierarchyKey] to RLS-Mapping[HierarchyKey]. Many to Many. RLS-Mapping filtering Employees

Next, I can add a new RLS role to the Data model:

Figure 11 – Add the new RLS role to the Data model (Figure by the Author)

That's all.

I can test the functionality for Andrea Madson:

Figure 12 – Testing the RLS role (Figure by the Author)

This is the result (Remember that Andrea Madson is responsible for the Sales in the Central region):

Figure 13 – The result of testing the RLS role for Andrea Madson (Figure by the Author)

The issue with the empty levels can be solved by filling them with the content of the level above. Some visuals can collapse repeating levels in hierarchies (Like Zebra BI).

But the standard Matrix Visual cannot do that.

Therefore, we have to live with this effect.

How does it work?

So, how does it work?

Let's look at the example of Andrea Madson.

In the Employees table, her row has the HierarchyKey "Evergreen Products_Sales_Sales Central__".

This is the same value in the RLS-Access table.

When we apply the RLS role to the RLS-Access table, this filter is applied over the relationship to the RLS-Mapping table on the RLS-Key column:

This filter results in the following rows remaining in the RLS-Mapping table:

Figure 14 – Remaining rows after applying the filter on the RLS-Key column for Andrea Madson (Figure by the Author)

The remaining values in the HierarchyKey column are then passed through the Relationship to the Employees table, and the remaining rows are part of the Sales Central unit in the organization:

Figure 15 – Filtered rows for the Organizational Unit of Andrea Madson (Figure by the Author)

This mechanism works for all other Units, as I have multiplied the rows for the RLS-Mapping table.

Conclusion

This approach allows me to implement a very simple DAX expression in the RLS role.

As explained in my last article about RLS (the last article in the list of links in the References section below), it's important to simplify the DAX expressions in the RLS role as much as possible.

The reason is that these DAX expressions are added to each DAX query sent to the Data model. Complicated DAX expressions can cause poor report performance.

So, the positive effect of modeling the data in the way explained here is that I can follow this rule.

Even though this means going the extra mile when modeling a Data model in Power BI and Power Query.

Your Users will not thank you for that, but they will not complain, which is even better.

References

The data is self-generated with phantasy names.

I generated this complete list by multiplying a list of First- and Last-Names with each other. Then, I generated a fact table based on random numbers.

Here are the links to my other articles about Data access with RLS:

4 + 2 Security features in Power BI

Develop and test RLS Rules in Power BI

Calculate the percentage of the total with RLS in place in Power BI

Explore all ways to implement RLS Rules

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: