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

2017-11-10_13h39_07

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.

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;

2017-11-10_12h22_54

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

User table

2017-11-10_12h34_13

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

Profile table

2017-11-10_12h35_29

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

User Profile table

2017-11-10_12h36_14

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

Transactions table

2017-11-10_12h38_32

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

2017-11-10_12h39_47

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()

2017-11-10_13h51_38

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.

2017-11-10_13h39_07

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”.

2017-11-10_13h48_05

 

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

2017-11-10_13h52_42

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

2017-11-10_13h53_35

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;

2017-11-10_14h10_34

As a result, this method works perfectly as expected;

2017-11-10_14h12_11

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.

2017-11-10_14h13_24

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 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 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.

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

  • Hi Reza!
    These RLS posts are really useful! Thanks for that!
    I myself have been using this simple way Kasper did: https://www.kasperonbi.com/power-bi-desktop-dynamic-security-cheat-sheet/ . It has its upsides and downsides, downside is that its not very user friendly to setup and manage, upside is that its fast – cause its so robust.

    I wanted to ask your opinion on the performance of different RLS techniques.
    Could you elaborate what happens when this technique in the article reaches 100.000.000 rows in the transactions table? (My guess would be that “IN” operator in the formula would create performance issues)

    • Hi Rainar,
      Thanks for your kind words.
      The method mentioned in Kasper’s post seems to use cross filter direction, IN method won’t be worse than that I believe, However I have not tested it yet with a table with 100M rows to see how it goes. I’ll write a post about performance of it as soon as I do that for sure 🙂
      Cheers
      Reza

      • Thanks for the answer! I’ll keep my eyes open for an RLS performance post – that should be interesting.

  • Hi,
    I have an query about provide data secutiry on tables inside power bi like SSAS. Specific users can see spacefic tables only.
    For Example, I have these following tables in my Power BI model –
    Primary Sales, Secondary Sales, Profit & Loss

    I am sharing the reports with my analyst team who can further customize or make new reports based on this data. The issue with this all analyst team members have the information about all the tables. I want only selected group can access the Profit & Loss table. Like we do this in SSAS through Perspective option.
    How can I do it in Power BI?

    • Hi Ankur
      Object level (table level) and column level security is available in SSAS. but unfortunately not yet in the Power BI itself. Your solutions would be either to use row level security in SSAS with live connection from Power BI. or alternatively uses some workarounds in Power BI that uses measures on top of columns and show respective data based on the role logic.

  • Hello Reza,

    This is a good example of RLS in many to many relation ship, thanks for sharing. I am working on five level organisation hierarchy and lowest level of hierarchy is site, here I have many to many relationship with user & site, mean one user may have access to many site and one site can be access by many users. Can you please share example implementing RLS in organisation hierarchy with many to many relation.

    Thanks
    Manoj

      • Hello Reza,

        Thank for your reply, Yes I read your organizational hierarchy RLS example and implemented it in my scenario. But I am not able to create DAX expression to combine both (organisation hierarchy & many-2-many). I am new to PowerBI and still learning about DAX. I would really appreciate if you write DAX for this scenario.

        Many Thanks
        Manoj

  • Hi Reza!

    Thanks for sharing this. I’ve used many of your ideas and recommendations in my reports and so far they have proved to work very well 🙂
    I have a non-technical question though.
    Where do you usually get tables User Profile and User from? The data source itself? Or are they tables you manually create in Power BI?
    I did a proof of concept where I created myself the User – profile tables in the CDS, but now that I need to scale up the solution, questions about compliance have been brought up.
    I strongly believe the CDS options is a good approach as it is flexible and I can have solution that it is complaint with the Internal Audit standards. However, I would like to hear your opinion.
    Are there best practices in that regard?

    Thank you so much! Cheers from Denmark.

    • Hi Felipe,
      Thanks for your kind words
      Very good question!
      I try NOT to create the table in Power BI, it is easy for POC as you mentioned, but maintaining that table, inserting rows in it I mean, means I have to open the PBIX file each time, change it and publish it. this you should avoid.
      I suggest to put it in a data source that is easily accessible with minimum configurations, SQL Server (Azure), CDS, Excel files in OneDrive for Business. they are all good options because you don’t even need to set up a gateway.
      and most of the customers would be able to spin up such a data source very simply, sometimes at no additional cost.
      Cheers
      Reza

  • Reza,
    How do you do a measure benchmark when you implement Row-level security. Basically I want to compare the user’s sales with the department or company sales dynamically. Can you please provide some details on how to accomplish that?
    Thanks

  • Hi, I tried to implement this but the filter is not working it seems. I added the expression in the Manage roles just as shown in the post. But the values are not showing correct. But if i apply both directional filter between user profile and profile, it is showing correct. Any suggestions?

Leave a Reply