Dynamic Row Level Security with Organizational Hierarchy Power BI

In previous posts, I covered some methods of Dynamic Row Level Security including Row Level Security user based, With Manager Level Access, and also with User and Profiles as a many to many relationships. In this post, I’m going to cover another common scenario for row level security; Organizational hierarchy for security. Through organizational hierarchy the approach is that each employee should have access to his/her own data, and manager should have access to employee’s data, there might be another higher level manager as well. every person should have access to all employees under him or her. In this post, we are going to cover this method of security in details with Power BI. 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

Every company has an organizational hierarchy, in many scenarios employees needs to be authorized to their data records only, and to the data of people whom they are managing. Here is an example of organization chart;

In the organization chart above; Bill should see only one record of data. Mark should see three records; 2 records for himself, and 1 record from Bill (because Bill is reporting directly to Mark). Leila should see 4 records; one record for herself, 2 records for Mark, and one record for Bill. This is how the hierarchical organizational row level security required to work.

User Table

Here is the sample data in User table;

as you can see we have two main columns; ID of the employee, and the Manager ID which points to the record which is the manager’s record.

Sales Transaction Table

For every employee there might be one or more sales transactions in the transactions table;

Diagram of the model

two tables here create a very simple diagram as below;

Sample Report

Here is a glance of sample report; it has all records from all employees as well as the organizational hierarchy;

Path Functions in DAX

For implementing row level security in this scenario, one of the most common ways is using Path functions in DAX. Path functions are powerful functions that can navigate through an unknown level of hierarchy based on an ID, and Parent ID structure. the structure of your data table usually is constructed based on two columns; ID and Manager ID as below;

To learn how path functions are working, let’s explore couple of these functions;

Path()

This function will go through an ID, and parent ID structure, and reveals the whole hierarchical path into a string type delimited style. to use this function you can simply create a calculated column in the user table with below expression;

Path = PATH(User[ID],User[Manager ID])

This function will give you the whole path for the hierarchy with a delimited text value. The id of every employee in the path is separated in this text by a vertical line (|).

PathItem()

The PathItem() function will give you the specific item in a path. if you want to see who is the manager level 1, or level 2 or 3, you can use PathItem to fetch it. Here is an example;

PATHITEM(User[Path],2,1)

In the code above; 2 is the index of the level (2 means the second level of management), and 1 is the data type of output (1 means integer).

You can then combine this method with LookupValue function to find out name of the person in that level;

LOOKUPVALUE(
User[Name],
User[ID], 
PATHITEM(User[Path],2,1) 
)

This means you can create calculated columns for every level of organization hierarchy;

PathContains()

Now the important function of this post is PathContains. PathContains will check if an ID exists in the path or not. This is the function that you need to implement row level security. All you need to find out is the ID of the person who is logged in. We already know how to get the email address of the person who is logged in, we use UserName() or UserPrincipalName() function for it.

Find out the ID of person Logged in

You can use a Filter function and Iterator function in DAX to find out who logged in to the system, filter function for filtering the user with the PrincipalUserName() function, and Iterator function fetched the ID of that remaining record.

Here is how Filter Function used to fetch the logged in user’s record;

Filter(
User,
[Email]=USERPRINCIPALNAME()
)

After finding the record for current user, you can use MaxX or MinX to find out the ID of that user;

MaxX(
Filter(
User,
[Email]=USERPRINCIPALNAME()
)
,User[ID]
)

and finally you can now use this ID in a PathContains functions to check if user’s ID exists in a path or not;

PATHCONTAINS(User[Path],
MaxX(
Filter(
User,
[Email]=USERPRINCIPALNAME()
)
,User[ID]
)
)

You need to add this logic as a role in User table;

This DAX expression will check the full path of organization hierarchy to see if there is any records in user table which has this user ID in their Path column or not.

Testing Result

As a result if you switch to that user, you will see only logged in user with records related to him/her

There are other methods of implementing such logic, you can use other functions and expressions to find the current records ID. This post explained one way of doing this. As you can see in the above screenshot, Reza only has access to see records for himself, Amy, and David (his direct reports), and Lindsay (who reports directly to Amy).

Summary

Applying row level security has many variations. In this post, you’ve learned about how to use organization hierarchy and Path functions in DAX to implement row level security based on a hierarchy. 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;

Dynamic Row Level Security with Users and Profiles

When each user can be part of multiple profiles, or each profile can have many users. This many to many situation creates a different approach for row level security. This post explains in details how that method of security can be implemented.

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.