What Do You Need to Implement Dynamic Row-Level Security in Power BI?

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

I have written some articles on the RLS topic already. Through my consulting experience, however, I get the feeling that many people can’t easily move from static RLS to dynamic. If you know what are things you need to have the dynamic RLS implemented, it makes the whole process simple. So this is what I intend to explain in this article; read through to learn what are the things you need to implement a dynamic RLS.

What is Dynamic Row-Level Security, and Why it is better?

At first, you need to know what is the dynamic RLS, and why it is better than static RLS.

Static RLS means that you define the logic of security inside the Power BI file (PBIX), and for every change in the logic, you have to open the PBIX file, apply the change, save the file, and publish it again.

Learn more about static RLS using the example below;

Dynamic RLS means that you define the logic of security inside the data model (tables, their relationships, etc). For a change in the logic, you just need to add/edit/delete records in the tables, and that’s it!

If you want to learn more about Dynamic RLS read the post below;

The definition above also says the benefits of dynamic RLS. If you want to add a role or change it, you don’t need to open the PBIX file, make changes and publish it again. It is all done through the tables.

In other words; Dynamic Row-Level Security requires less maintenance efforts in the future for the changes.

Users Table

In order to get the dynamic row-level security working, you need to have a table of all users. This table needs to have all users in it with a field which is their login id to the Power BI report.

If your report is hosted in the Power BI service, their login id is the email address that they use to login to the Power BI service. If the report is hosted in the Power BI report server, then the login id is the network account they use to login to the report server.

This is an example of a users table:

2017-11-10_12h34_13

I often get this question that; how can you produce this table? You can query this data from Office 365, or from Active Directory in many way, such as PowerShell scripts. Even in Power BI there is an option to get data from active directory.

Roles Table

You also need a table for roles. Remember that roles are defined dynamic inside the data model. The roles table is a table with a row for each different access level.

For example, If you want to have a different access level for each region, there should be one record per region in this table. This table can be called Profile table too. Here is an example of roles table;

2017-11-10_12h35_29

Note that sometimes you don’t need a separate table for Roles. Your Users table might act as a Roles table. In what scenarios you might ask. If you want to have one role per user, then you don’t need a separate Roles table. Your user table will act also as your Roles table.

For example, If you are implementing a Payroll Power BI report, you want every user to see their own data only. the user table and the role table are the same, you just need one user table. Here is an example of having only a user table as a role table;

However, if you may have multiple users with the same role, you do need to have a roles table. Here is an example for that;

The Users Table to Filter the Roles Table

If you expect every role to have multiple users assigned to it, you can have the users table to filter the roles table using a one-to-many relationship.

However, in many cases, the users and roles build a many-to-many relationship. In these scenarios, I do suggest to use DAX filter logic instead of the relationship to filter the Roles table based on the logged in user, here is an example.

Role Table Should Filter Other Tables

It is important that the role table should filter other tables in the data model. Filtering between Power BI tables is defined as a relationship. You need to have a relationship with a proper direction to the other tables in the data model from the roles table.

Here is an example of when the roles table is the same as the users table and filtering the sales table;

Here is an example of roles table (which is separate from the users table) filtering the sales table;

There is a tendency among many people to make some of the relationships both-directional to get the RLS working. I highly recommend you for not doing this. There are better ways to implement it which performs much faster such as the method I explained in this article.

DAX Filters

Another essential element of dynamic RLS is the DAX expression you write in the role as a filter. The purpose of this DAX expression it to get the user’s login id, and filter the Users table in your model. The DAX expression often uses UserName(), UserPrincipalName(), or CustomData() functions to get users’s information.

Here is an example of a simple DAX filter:

2016-07-04_22h54_07

and this is another example of a DAX filter:

2017-11-10_14h10_34

Sometimes the filter needs to be applied on not only one table (users table), but also some other tables (Roles and others);

Summary

Although every dynamic RLS implementation can be different based on the logic and the data model, there are some fundamentals that you need to have in every model. In this article you learn about some of those. If you are implementing dynamic RLS, you need to have;

  • A users table with the login id of all users
  • A roles table with the definition of all roles
  • The users table should filter the roles table
  • The roles table should filter other tables in the data model
  • DAX Filter

If you are interested in reading more about RLS scenarios, check out my other articles here:

Video

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

Leave a Reply