I have written previously about Dynamic Row Level Security, and some of the patterns of it. Two of the most common patterns that I explained were; Organizational Hierarchy, and Many to Many situations of users and profiles. Recently I came across a couple of questions, which persuaded me to write about another pattern of dynamic RLS; When we have multiple positions for some users in the company, and each position is part of an organization hierarchy. When the user logins, we want him/her to see information about all his/her positions, and also the tree of positions under his/her organizational hierarchy. Don’t think about this situation as a rare one, you already know some people in your company who take more than one role, and have more than one manager then. So let’s see how is it possible. If you like to learn more about Power BI, read Power BI book from Rookie to Rock Star.
The sample dataset for this example can be downloaded from here.
Good reads related to this article
I have written several articles about Row Level Security in Power BI, I advise you to have a good read on articles below;
Row Level Security in Power BI
Dynamic Row Level Security with Manager Level Access
Dynamic Row Level Security with Users and Profiles
Dynamic Row Level Security with Organizational Hierarchy
Row Level Security with Analysis Services Live Connection
Every organization has a hierarchy of employees based on their roles. Here is an example of such hierarchy:
However, the hierarchy is not always that simple. Sometimes (we can even say in most of the organizations) there are some people who have multiple roles. sometimes because they have been filling another role in the meantime while a replacement comes through. So the situation then will be like this, we will have a User table like below:
We also have a table for positions and organizational hierarchy as below; Organization Table;
The Manager ID column in each row is a link to the ID field of another record in the same table representing the organizational hierarchy. the hierarchy of organization looks like below in action:
Our transactions are related to the organization table. Let’s assume we have sales records related to each role; Sales Transaction Table;
As you can see, the transactions are related to Organization IDs not to the users specifically, because each user might be part of two organizations (multiple positions), or one organization (position) might have multiple users assigned to it.
The last table is the table that creates the many-to-many relationship between users and organizations (positions), User Organization table;
Here is the data model with the relationships;
If for example in this dataset, Reza Logins, his user ID is 2, which is related to the organization role CFO, and should not see only transactions related to CFO, but also to other roles underneath it (Finance Lead, Accounting Lead, and etc).
If Jack logins he has two organizational positions (Accounting Lead, and Accountant), and should see transactions related to both roles and also everything else in the hierarchy underneath these roles.
Dynamic Row Level Security means we get the username logged in using a function such as UserPrincipalName() or UserName() in DAX, and then filter tables based on that. If you like to learn about the basics of Dynamic RLS, read my article here. Now that you know the scenario above, let’s talk about what is the challenge we are facing in this implementation for security.
If we implement Dynamic RLS filtering in the user table; when a user such as Reza logins, then that table will be filtered and have only Reza’s record in it, as a result the User Organization table will be also filtered and will have only organizations that Reza is part of it. However, because of the single-directional relationship between User Organization table and the Organization table, the filter won’t pass through the rest of the model. As a result, this user will see all organizations and all transactions regardless of RLS implemented in the user table! diagram below shows this situation.
The many-to-many relationship in a model, will bring the need for the both-directional relationship, which is not recommended.
If we change the relationship to both-directional then, we get another issue; Reza logins, he is associated with CFO organization, so will see the CFO only (because the both-directional relationship passes the filter), and will see only transactions of CFO. This is not what we want for this requirement.
Reza should be able to see transactions of Finance Lead and Accounting Lead and other roles underneath. The both-directional relationship to the Organization table will filter the Organization table only for the current user’s positions.
The both-directional relationship and organizational hierarchy doesn’t work well with each other, as we need to see the entire tree of organization underneath.
The both-directional relationship is not the solution, so I change the model to the single-directional as below;
Now that you know the challenge and problem, let’s see how to fix it. The realm of dynamic row level security is the realm of DAX, there is nothing to stop you to achieve what you want there. Your ability is only limited to your DAX skills. This problem can be solved using DAX too. However, the expression will be a bit long. To make it easier to understand, I break it into parts and will go through it step by step;
The realm of dynamic row level security is the realm of DAX, there is nothing to stop you to achieve what you want there. Your ability is only limited to your DAX skills.
Step 1: Fetch the logged in user’s email address
Let’s start with fetching the username; the measure below shows the logged in username; Using the UserPrincipalName() DAX function;
Measure = USERPRINCIPALNAME()
Step 2: Fetch the ID of the current user
Now as the second step, we need to find out what is the ID in the User table for the person logged in, which is achievable using LookupValue() DAX function;
Measure = LOOKUPVALUE( Users[ID], Users[Email], USERPRINCIPALNAME() )
LookupValue function has three parameters, the column that we want to fetch the value of it as output (ID), the column that we are searching for a value in it (Email), and the value itself (coming from UserPrincipalName() function). As a result, this shows the ID of the user logged in to the system;
Step 3: Fetch all Organization IDs which is associated with the current user
As the third step, we need to fetch all organization IDs (from the User Organization) table, which are associated with the current user. That means filtering the User Organization table where the User ID field matches the output of the previous step. We can achieve that using Filter() function in DAX;
Measure = FILTER( 'Users Organizations', 'Users Organizations'[User ID]= LOOKUPVALUE( Users[ID], Users[Email], USERPRINCIPALNAME() ) )
Filter function gets the table as the input (User Organization), and then the filter criteria, which would be the User ID equals the output of the previous step calculation.
This gives us all rows in the User Organization table which their User ID is equal to the ID of the current user. However, because the output of Filter function is a table, you cannot show it in a measure, that is why we get the error above. In this step, we are still in the middle of our way towards final calculation. However, if you like to see what is the output of this so far, you can use the method below;
ConcatenateX a method to see some of the values in the table
This step is not part of the whole expression. It is just to show you how we can fetch the list of Organization IDs from the current table output of the Filter function using ConcatenateX (you can skip this step,)
Measure = CONCATENATEX( FILTER( 'Users Organizations', 'Users Organizations'[User ID]= LOOKUPVALUE( Users[ID], Users[Email], USERPRINCIPALNAME() ) ), 'Users Organizations'[Organization ID], ",")
ConcatenateX gets the table as input (the output of the previous step), then the expression that we want to concatenate it (the Organization ID column), and the text that we want to add between every two expressions to concatenate (“,” as a comma separator).
As an example, if we have Leila logged in;
Her user ID is 1, and she has two Organization rows associated with her account, which will be the output of our calculation above;
Unfortunately, I couldn’t explain the entire process in one post, so the rest of this comes as the second part here:
Dynamic Row Level Security in Power BI with Organizational Hierarchy and Multiple Positions in Many-to-Many Relationship – Part 2
9 thoughts on “Dynamic Row Level Security in Power BI with Organizational Hierarchy and Multiple Positions in Many-to-Many Relationship – Part 1”
“Your ability is only limited to your DAX skills.”
You are really champ on power BI. I want one help from you like i have created organization level hierarchy. and now my requirement to give access to one person to view all report. can we do reza.
You can either create a role with no condition, or use the IsManager method I explained here
you are a rockstar 🙂
Your link description to part 2 is a circular reference because it refers back to itself:
Dynamic Row Level Security in Power BI with Organizational Hierarchy and Multiple Positions in Many-to-Many Relationship – Part 1
Should say part 2. The link itself works though. Just wanted to mention the typo in case you wanted to fix it.
Good catch. Fixed it now. Thanks
I have a case that is like so that I have only Organization and User organization tables, and should create row level security like you described. But I cannot use the Path function in organization, because there is only organization number and name, in user organization has also the organization number. One user can be part of multiple organization ….
can you please share a screenshot of your data and tables?
This helped a ton, thanks!