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.


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.


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;


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;


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


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;

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;

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



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;

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

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

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


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 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 nine 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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

20 thoughts on “Dynamic Row Level Security with Organizational Hierarchy Power BI

  • Hi Reza,
    Thanks for the post.
    What if I would like to drill down into each level of managers below me to see the data for managers at each level?
    I am the manager at the top of the organisation. I have 5 managers directly reporting to me. And each manager has a various number of direct reports some of which are not managers. There are 20 levels in this hierarchy.
    There are 10 KPIs for each manager in the organisation and all KPIs are based on Direct reports of the manager in question.
    How can I see my KPIs based on my 5 direct reports and drill down into different levels to see KPIs for each manager at each level?

    Any idea is greatly appreciated.

    • Hi Omer.
      Thanks for your comment.
      Do you mean you want to see the report from your direct report’s point of view?
      or if you mean something else, can you please explain a bit more in details?


  • Reza:

    Outstanding post as always, one I’ve had bookmarked for a while now, and am finally going to dive in and apply!

    One question: what is the slicer visual you are using for the Organizational Chart in the sample above? Is it the Hierarchy Slicer custom visual?

      • Hi Reza,
        Its pleasure to look around dynamic Rls with managerial level hierarchy now to add more than I need to block a few columns eg: like few sales reps, sales amount should be blocked as well other reps fiscal year should be blocked. we are using capacity with 1000+users.
        we have the internal application developed wherein the Admin portal it has block access I have attached a snap for same.
        where admin can block all specified roles at one place whether power bi admin portal can achieve in user management.
        Thank you,

        • Hi
          Column level security is not possible through Power BI (YET), and we have to wait for it.
          if you use SSAS live connection, then you can do column level security there.
          an alternative to do it in Power BI is to hide your original columns, and create measures that based on conditions reveals a result or return blank

  • Hi Reza,
    Path function works well with import mode it does work with direct query mode. Is there any alternate function to go within DQ

    Thank you

    • When it comes to DirectQuery, you have to deal with t-sql functions. You can write recursive CTE queries to do the same process for you. there are many codes for that already if you search through Google. There is also an option to enable unrestricted DAX measures using DirectQuery, but I do not recommend as it slows down the model significantly.

  • Thanks for this clear example.. Helped a ton… I have a question for you on the “organization char” that looks like a slicer, pictured next to the table of Sales Results..

    How is that created?

      • I got the custom visual… THANKS… pretty cool.. but not sure My data is setup correctly.
        I have an employee table that has EmployeeName, EmpID, SupervisorName, SupervisorID, … and some other employee data…

        I can’t seem to make this Hierarhcy Slicer do more than one Level of Supervisor and EmployeeName….

        Your pic of the hierarchy slicer shows multiple levels…

  • Hi Reza!

    This article was really helpful! I was wondering if it is possible to implement RLS using the path funciton with multiple “seeds” for example if you had multiple companies and you wanted to combine their org charts could you have a manager login to reference multiple groups of people? I have all of the company hierarchy in the same table wiht the path, a table of managers and the orgs they manage, and the data table. Currently the RLS will filter for only one org that the manager is assigned to not multiple. It reads the path correctly but not for all of the orgs. Is there some sort of way I could create a loop to look at all of the orgs the manager is assigned to? Thank you for your help!!

    • Hi Rachel.
      You can definitely implement whatever you want using DAX combination of functions.
      However, to give you a response more in details, I would really need to see the data model and understand what you are after. If you can, send me your sample data

  • Great article Reza,
    We have a similar situation but with one extra level of complexity.
    We have manager effective date which means an employee can change mangers and we need to take the dates in consideration.
    What would be the best approach to achieve that by extending this model please ?

    • Hi Michel
      That is an interesting scenario.
      Looks like a good challenge for my next blog post on RLS.
      Can you send me the sample dataset? I would like to see where you manage the effective date? do you have a separate table saying the manager of this employee is another employee at this effective date? send it to my email, which is my first name at domain of this website.


  • HI Reza Rad
    This article was very helpful for me. But my scenario is little bit different than this. My scenario and requirement is like this, GM(General Manager) having 5 managers under him, Like this i am having 5 GMs. Managers are having more than 20 direct employees reporting to them. Some Managers are reporting to more than 1 GMs(Example : Manager A is reporting to Gms A And B ). How can i implement this scenario?. Thanks.

  • Hi Reza I have power bi pro and I have to apply Row level security .but I donot see enable cross filtering in both directions for Direct query in preview features of power bi. can I apply Row level security without this feature? Please let me know. I am stuck.

Leave a Reply

Your email address will not be published. Required fields are marked *