Dynamic Row Level Security with Profiles and Users in Power BI : Many-to-Many Relationship

The Dynamic RLS topic is getting a good response in my blog post, so I intend to write about other types of scenarios that a security would be required. In this post, we are going to look at another type of security which deals with users and their profiles. Sometimes you have a user part of multiple group (or profile), and also a profile contains multiple users. This Many-to-Many relationship needs to be also incorporated in the way that security works in Power BI. You want all users to see data related to their own profiles. Because this model includes a Many-to-Many relationship, the implementation of that is a bit different from the normal Dynamic RLS. to learn more about Power BI; read Power BI book from Rookie to Rock Star.

Video

Prerequisite

For reading this post, it would be much better to have an understanding of the simple dynamic row-level security with Username or UserPrincipalName function beforehand. please read this post.

Scenario

Imagine Mark is working in a company. Mark is part of the Sales Group in Seattle. He should see all sales transactions related to Seattle. Also; he is part of the sales group in Portland, and should see the transactions details of Portland too. On the other hand side; there are other people which their access is different. David is part of sales group of Chicago, and also Seattle. This situation creates a Many-to-Many relationship between user table and profile table such as below;

To be able to follow the example, here is sample data sets for each table;

User table

It is important that user table includes a column for their Power BI accounts (in this example; email).

Profile table

You might call it branch or store or group as well.

User Profile table

This table holds the relationship or link between users and their profiles.

Transactions table

Every transaction is related to a profile. All users under that profile should have access to the transaction which marked for that profile.

Full Diagram of the Model

The difference between this model and a simple dynamic row-level security is that in this model we have a Many-to-Many relationship, and filter propagation would not be as easy as the simple model.

Filtering Users doesn’t work

Filtering users itself won’t be a solution in this case. Here is a role defined in a user table with DAX filter below;

[email]=UserPrincipalName()

This DAX filter on the User table would filter the User Profile, but it won’t filter the Profile table, so as a result, it won’t filter the transaction table.

The direction of the relationship between Profile table and User Profile table doesn’t allow the filter to propagate from User Profile to the Profile table. As a result, the Sales transactions table’s data won’t be filtered with the DAX filter defined in the user table. There are many methods that you can get this solution working. In this post, I’ll explain two methods;

Cross Filter; or Both Direction Relationship

This method is not recommended especially because of the performance issue, However, I like to explain it as the first method, because it helps to understand how relationships work in DAX. For using this method; you need to change the direction of the relationship between Profile table, and User Profile table to both directional, and also you need to check the option for “apply security filter in both directions”.

After this change your row level security configuration will work perfectly fine as below;

The diagram of model is now passing the filter all the way to sales transactions table;

This method is easy to implement. However is not recommended. Applying both direction relationship and the security filter will slow down the performance of your Power BI solution significatnly. On the other hand side; if you have many tables related to each other; depends on the circular dependency, you might not be able to apply both directional relationship always. So I do recommend the next method.

Filtering Data through DAX

Because of issued mentioned in the previous method; I do recommend using this method. This method is basically passing filters through writing some logic within DAX. There are many methods of implementing this logic with DAX. You can write a LookupValue function, Filter function, or many other ways. I’ll explain it with the Filter function.

In this method; you don’t need to change the direction of relationship to be both directions. In this method, you need to find a way to filter data in the Profile table. If you filter the data in Profile table, then the data in Sales Transactions table will be filtered automatically. One way of filtering is to find out first all profile IDs from the User Profile which is related to the logged in user;

Step 1: Find all rows in User Profile for the logged in User

You can use a simple FILTER() function to get all rows from User Profile for the logged in user;

FILTER(
	'User Profile',
	RELATED(User[Email])=USERPRINCIPALNAME()
)

This code will return a sub table from User Profile table which are only rows that is related to the logged in user. from this list, let’s get only their Profile ID in the next step;

Step 2: Get Profile IDs from the List

You can use SelectColumns() DAX function to only select Profile column (which is the ID of the profile);

SELECTCOLUMNS(
	FILTER(
	'User Profile',
	RELATED(User[Email])=USERPRINCIPALNAME()
),
"Profile"
,[Profile]
)

Now this DAX code will return a table with a single column which is the ID of the profiles.

Step 3: Filter Profile table where the ID matches the table above

we can now use IN keyword to filter data of Profile table just for the logged in user;

[ID] IN SELECTCOLUMNS(
	FILTER(
	'User Profile',
	RELATED(User[Email])=USERPRINCIPALNAME()
),
	"Profile"
,[Profile]
)

This filter should be written in Profile table as below;

As a result, this method works perfectly as expected;

Diagram of this method is a single directional relationship. However, the DAX Filter propagates all the way with the logic we’ve written in the role.

Summary

Applying row level security has many variations. In this post, you’ve learned about how to use Users and Groups (or Profiles) concept and overcome the Many-to-Many challenge for the row-level security. You’ve learned about two methods; which one of them was the recommended approach. There are other ways to implement this scenario as well (with LookupValue and many other functions). Please share your row-level-security scenario down in the comment below if it is different from what discussed. to learn more about other ways of row-level security, read the post series here:

Static Row Level Security

The logic of security is static in the role definition, we call it Static Row Level Security. to learn more about it, read this blog post.

Row Level Security in SSAS Live Connection

In this case, the Power BI Report connected live to an SSAS model. the username will pass through effective username connection information, to learn more about it, read this post.

Dynamic Row Level Security

When you have too many roles, then implementing static roles is not an option. You need to create one role and maintain the logic of security within the data model. This is called Dynamic row level security. To learn more about this, read this blog post.

Manager Level Access in Dynamic RLS

The dynamic RLS example that I explained in this post, does not include the manager level access. Sometimes you need the manager to have access to all data. This post explains how to do it;

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 12 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, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.