How to implement Row Level Security in Power BI (Part I)

9 min readMar 21, 2022

Row-Level Security is a way to filter the data differently depending on different roles. This might be done statically, as only showing data for Sweden for the sales manager for Sweden, or dynamically, which is based on who is signed in to Power BI.

You can also find the article on my web page eliasnordlinder.com, along with other Power BI aspects such as Resources, DAX-snippets and more.

Short introduction to Row-Level Security

What is Row-Level Security?

Guy in a Cube in describes Row-Level Security as:

“A way to restrict data based on the logged in user”

Microsoft describes it as:

A way to be able to “use group membership or execution context to control access to rows in a database table”
A tool that “simplifies the design and coding of security in your application”

I would explain it as:

A tool that simplifies the life of data governance and increase the security of your reports.
A way to share a report in a lot of different ways, without copying or creating separate views manually to different users

Why do you want Row-Level Security (RLS)?

The main reason for RLS is, as you can hear in the name, security. RLS an important part of data governance and to secure the data that you are sharing from Power BI.
By being able to restrict the data for users, you can have data models that contains confidential data, but only show restricted data to specific users.
If you would not leveraging RLS, you would have to create a lot of copies of the same report to different people. RLS is therefore also great for making the data governance easier and more correct.

Common example for RLS (Sales Bonuses)

The manager want to see the bonuses for his or her whole department, say Sweden.
All the sales people in the department should only be able to see their own sales, and sales bonuses.
By using RLS, the same report and data model can be used both for the sales people as well as the manager-
Through the implementation of RLS, the manager will see the sales and bonuses for the whole department, while the sales people will only see their own data.

Different types of RLS

There are two main types of RLS:

Static RLS

You implement the security with a static rule such as, [Country] = “Sweden”
Have to create one Role for each specific view

Dynamic RLS

You implement the security with an inbuilt function in DAX, [Username] = USERNAME()
Create one Role in total, and filter the data based on logged in user to create different views

Implement Static Row-Level Security (RLS) in Power BI practically?

The following steps can be taken to implement static row-level security:

Decide Which Dimension to Implement RLS on
Create a role in Power BI Desktop for RLS
Test RLS in Power BI Desktop
Publish the model and report to Power BI Service
Add a person to a specific role in Power BI Service
Test as Role in Power BI Service

1. Decide which dimension to implement Row-Level Security on

We have decided to implement Row-Level Security on the dimension city and have data that looks like below

We have specific sales managers in each city that should only see the city where they are stated.
As an example in this article we will create a role for the manager of the city Springfield

2. Create a Role in Power BI Desktop for RLS

Click Modelling in the ribbon
Select “Manage Roles”
Click “Create”
Name the rule to something, like “Springfield”
Click the Table that has the specific dimension you want to filter on, in this example Store Regions
Write the specific filter, here [City name] = “Springfield”
Click the check box above to double check that you have written correct DAX
Click Save below

3. Test RLS in Power BI Desktop

After that you have implemented the Row-Level Security above you can try out if you have implemented the Row-Level Security correctly.

It can be seen that when using the role “Springfield” one can only see Springfield, therefore the role looks correct.

4. Publish the model and report to Power BI Service

The dataset and report is now published to Power BI Server

5. Add people to the role that you have created

The next step is to add people to this role “Springfield”, so they will only see this data.
One have to add this Row-Level Security inside Power BI Service.
Press the three dots besides the dataset, and press security.
When you get to the next page, press the specific role and write the email address of the person to add and press add.

6. Test as Role in Power BI Service

The final part before publishing the model to the organisation is to test the role in Power BI Service as well, to be sure that everything works.
Press “Test as Role” inside Power BI Service
You will now see the workbook with the role attached

It can be seen once again that only Springfield is showing and the Row-Level Security therefore works!

You have now learned how to implement Static Row-Level Security in Power BI.

But, you might think as of now, what if there were 100 cities, and every city had a specific sales person as for Springfield above.

Do I need to add 100 roles with 100 different people for this to work?

The answer is yes, if you only work with static Row-Level Security
The solution for this problem is instead to implement Dynamic Row-Level security which is discussed in the next passage..

Implement Dynamic Row-Level Security in Power BI practically?

As hinted in the end of last passage, dynamic RLS can handle a large number of roles in a better way than static RLS.

The specific attribute of Dynamic RLS is that makes use of specific functions in Power BI (USERNAME(), USERPRINCIPALNAME) that gather information on who is signed in to Power BI Service

It is easiest to explain Dynamic RLS by doing.

The following steps can be done to implement Dynamic RLS:

Create a Loookup table which contains

Username
Seller ID
Name (Optional)

2. Connect this Lookup table to a fact table by some key, Seller ID in the example above

3. Create a role that filters the username column based on a DAX measures

4. Add two tables to a visualization to be able to test if dynamic RLS works in Power BI Server

5. Publish to Power BI Service

6. Add user to Role

7. Test in Power BI Service

Create a Loookup table

Simple lookup table to use in the model for later for filter propagation with the help of Row-Level Security

2. Connect this Lookup table to a fact table by some key, Seller ID in the example above

The Lookup Table is connected to the Fact Table to be able to filter it for later

3. Create a role that filters the username column based on a DAX measures

We make use of the USERNAME() function to filter the column Username in the lookup table.
The row in the lookup table will now only be the one that corresponds to the signed in user in Power BI -> Magic!

4. Add two tables to a visualization to be able to test if dynamic RLS works in Power BI Server

First table is just the lookup table in the visualization
When we test the role later in Power BI Service, we will hopefully only see one row with the same username as the signed in user
The second table is similar as for the static Row-Level security, showing the Sales Representative as well as the sales, which will hopefully only be one bar in Power BI Service.

5. Publish to Power BI Service

Nothing special here, same way as for the Static RLS

6. Add user to Role

Add a user to a specific role in the same way as you would do for Static Row-Level Security.

7. Test in Power BI Service

As we can only see one username in both the table and the chart the Dynamic Row-Level Security works!

To be Continued…

In the next part I will go into more advanced concepts of Row-Level Security such as:

How to work with Managers and Employees with Dynamic Row-Level Security
How to handle Many-To-Many relationships in Row-Level Security
How to handle other hierarchies with Roe-Level Security
How to implement Row-Level Security in SQL Server Analysis Services

Conclusion

In this article I have shown the basics in how to implement Static Row-Level Security and Dynamic Row-Level Security:

What is Row-Level Security?

Guy in a Cube — “A way to restrict data based on the logged in user”
(True If using Dynamic RLS)

Static Row-Level Security

Works with simple DAX measures: [Sales Region] = “Sweden”
Works in small organization, but becomes complicated in large organizations, where many different roles are needed, as the need becomes to add people to all these different roles

Dynamic Row-Level Security

Uses data from who is signed in to Power BI and uses the report
Needs to use specific DAX measures and a Lookup Table that is connected to the model: [Username] = USERNAME()
Works well in large organizations when a lot of different views are needed, as only one role has to be created.

Row-Level Security Part II

There will come a part 2 in the future with more complex ways to implement Row-Level Security than the ones mentioned above

References

Curbal

Row Level Security in Power BI desktop

Guy in a Cube

What is Row-Level Security (RLS) in Power BI???

Microsoft

Row-Level Security

Reza Rad

Row-Level Security in Power BI

Questions or inquiries

If you have any questions or input please contact me on

LinkedIn: www.linkedin.com/in/elias-nordlinder
Email: Elias.Nordlinder@gmail.com
Webpage: eliasnordlinder.com

Have a great week everyone
//Elias