Dynamic Row Level Security in Power BI with Organizational Hierarchy and Multiple Positions in Many-to-Many Relationship – Part 1

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.

Prerequisite

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

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

Scenario Explanation

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;

Sample

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.

The Challenge

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 Solution

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;

Next Steps

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

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.