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 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.
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?
Cheers
Reza
Hi Reza,
Thank you very much for this technique, this helped me implement row level security in a project that is being undertaken throughout the organisation. The only problem I have here is that that I am not able to hide the higher ups name when its not required.
For Example: In the above example, if Lindsay logs in, she would be able to see Amy’s name in the org filter, I would not want that to happen, instead, Lindsay should only see her name and her direct reports and in-direct reports.
Is this possible? I tried the web and couldn’t find any meaningful solution, any guidance here would mean a lot for my personal learning and simplicity of my dashboard.
Sincerely,
Raj
Hi Rajesh
you can hide the column that belongs to higher-level managers. at the end of the day, that is just the name of the manager, and not other information from that user
Cheers
Reza
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?
Thanks for your kind words.
Yes it is hierarchy slicer 🙂
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,
nag
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
Cheers
Reza
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
nag
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?
Hi Ray
That is a custom visual called “Hierarchy Slicer”
Cheers
Reza
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 again,
you need to first create all columns of your hierarchy.
I explained it here
then you can create the hierarchy and show it in the custom visual 🙂
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
Cheers
Reza
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.
Cheers
Reza
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,
I have explained about that scenario too. When the organizational hierarchy and many-to-many scenario happens together. You can read it here.
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.
Hi Bishnu
I would NOT recommend enabling bi-directional filtering anyways! here I explained a work around for that which works better
Cheers
Reza
Hello Reza,
We are trying to set up this logic to manage security in our datamodel. Everything seem to work fine when im in PowerBI Desktop. When I try the “View as Roles” option, my data is filtered as it should be. I then publish the model and assign the roles to my email address to test it (im in the user table, so I should be able to see some data but not all of it). But when I open the report, surprise surprise, I can see *all* the data in the model ! To me it seem like the data model itself is not the problem, but rather that my Email address is not assigned correctly to the role. Am I right to think so? is there something I missed?
Thank you !
Hi Jean
The account that publishes the Power BI report, is the owner of Power BI report. Owner of the report will see everything, regardless of RLS.
You can test the RLS functionality using accounts that have read-only access to the report.
Cheers
Reza
Hi Reza,
Thank you for all the posts – they’re fantastic!! I do have a question about this method for Dynamic RLS on an organizational hierarchy. My question is very similar to Jean-Francois’ question. I have my report built in Power BI with a Role defined in my User table. When I use the “View as Roles” feature in Power BI Desktop and manually enter one of the email addresses of a user in my User table everything works as expected. However, when I publish the report and have a co-worker run it who is also in my User table as a manager, he sees all the data, instead of only the data of those he manages.
Am I not understanding how Roles work in Power BI? My assumption was by simply creating a Role with the DAX code from your example (PATHCONTAINS(User[Path], MAxX …) as a filter on the Role on my User table, the user would automatically be recognized and the data filtered accordingly.
What am I missing?
Thanks!
How do you share the report?
if the user has EDIT access on the dataset/report, then regardless of row-level security, he/she will see everything! That means if you create a workspace, and add the user as the member or contributor of the workspace, then the user will see everything.
To get the RLS working, the user should have VIEW ONLY access to the content.
Cheers
Reza
Hi Reza, your posts are great, have helped me immensely. Thank you. Question on sharing the report via Apps- if RLS is implemented on one of the reports in the published App, what is the right way to provide the respective Role level access to users – add them via Workspace as Read only users OR Add them at App? Thanks again.
Hi Sirisha
it depends on what type of users they are?
if they are end users, then just share the app with them
if they are report visualizer, you need to give them ability to create reports on the underlying dataset
Cheers
Reza
Thanks Reza. I didn’t know users had to be Viewers in order for RLS to work. I added a few Viewers to our workspace and … it worked!
Thank you!
Hi Brad
Glad to see it worked.
RLS only works for view-only users. so in all methods of sharing, consider the user to have view-only access and then it would work fine 🙂
Cheers
Reza
Hi, This was really helpful, but i am facing a problem. I have designed a hierarchy like,
-A
-B
-C
-D,
Where B,C,D report to Manager A. In this case D is my id. After publishing the report,all the layers follow rls except D. Its shows the same as A. But when trying as a test as role D follows Rls a deifned. Could you explain me to solve this
can you share a screenshot of your tables, their relationships, and the data in each table?
Cheers
Reza
Hi Reza,
This is a super useful explanation of RLS. I have a question on how would you implement RLS if you do not have any data for Managers in the Sales Table? Do I need to add a $0 row to sales table for the Mangers present in User Table? Or there’s a better way to do it?
Hi Abhi
I believe this is a question not about row-level security but about how to select a manager and see the total sales of his/her department, am I right?
in that case, you need a calculation that uses Path functions in there, something like PathContains, getting all the children of that manager, and then calculate sales of those.
Cheers
Reza
Hi Reza,
When i implement and publish it , users who are member to that work space (they can seee other reports fine )gets error ” This visual contains Restricted Data ” … contact the dataset owner to access data behind it . Do i need to add users to role in power bi. On desktop it works fine, i tested using diff users and there managers
Do i need to do any other step too, add users to role etc
Thanks Tyron
Hi Tyron
when you implement dynamic RLS, all the users should be part of the role, otherwise, they won’t see the report, even if they are in the same workspace
Cheers
Reza
Hi Reza! This worked fantastic and is exactly what I needed when I found it. I have since created a report that has information that we would want the Hierarchical manager to see for their rollup, but Not for themselves (for example, private talent review information). Is there a way to exclude self from your own path? Using dataset example above would be Reza can see David, Amy, Lindsay but not Reza?
Hi Libby
Thanks
I am interested to know what is the scenario that they want to see the information of all their tree, but not themselves?
Yes, definitely possible. You need to add another condition in the DAX expression that also checks if the email field is same as the userprincipalname function. that way, you know that what is THEIR OWN record, and have a different result for that
Cheers
Reza
Hi Reza,
I have the same question. How should I go about excluding self from their own path?
Could you please explain it in more details?
Thanks so much.
Hi Claire,
Not sure which question you are referring to, but if your intention is to see transactions of all your team but not yourself, then yes, you can add a condition that removes if the total path is the ID of the user only
Cheers
Reza
Reza,
Great article! I’m trying to apply this to something I am working on now. The report I’m developing is intended to be viewed by my sales organization where the Dynamic Role Level Security will be ideal. However I have non-Sales users (ie Finance, Sales Ops, Operations) who need to see all the data. So in terms of hierarchy these other non-Users would be at the top of the hierarchy. How do you handle those users? Do you use a different role?
Thanks
Hi Tyler
Won’t they be added in the organizational hierarchy as a top level manager then? because that way, they will see everything
Cheers
Reza
The administrative employees often have full access to everything but are not at the top of the hierarchy. I guess that could be solved by another role OR by using the IsManager logic you described in another post. (“IsEconomyEmployee”)
Hi Remi
The best would be not to add another role
use the logic of IsManager is a great way to do that. you can call it IsAsministratorEmployee
Cheers
Reza
Hi Reza,
First of all, thanks for this article. This is very helpful.
I have a similar requirement, and i followed every step as in your post. It is not working for me when i test from ‘view as roles’ section. (Note: EmployeeID and Manager ID values in my data set are like CEO1, Director_HR_01, Director_HR_02 etc. But it shouldn’t affect the formula, right?)
When i tried to breakdown the formula and validate step by step in DAX, i am getting below error message at the PATHCONTAINS step.
”A table of multiple values was supplied where a single value was expected”. I am losing my mind over fixing this, and posting it here as a last option.
Appreciate your help.
HI Rav
I need to see your table structures and also your DAX expression to help.
Cheers
Reza
Great information. Worked really well.. Quick query – after applying this user level security, if I also want to show comparison with total volume/data as well, is it possible to implement.
Basically after using this settings, I can only see user level data but I want to show overall details as well (average of firm level data)
Please read my article here about comparing with the total in RLS
Cheers
Reza
In the above example, when Mark log in , level 1 shows as Leila, is there a dynamic way to show level 1 as Mark (person who logs in) and ignore the hierarchy above him
if you don’t want to see anything from the higher levels, you can create a calculated column that always replaces the upper level’s value with the level of the current person.
Cheers
Reza
Thank you for this post Reza, this is exactly what I was looking for.
Can you help me on the following question? What if I want to give someone access to two separate teams that are not in the same path?
Ie. only access to Mark’s employees and Amy’s employees. Is that possible?
Hi Daniel
This is the article that I explained that scenario
Cheers
Reza
Hi Reza,
i am using the above solution but i have one issue. what happen if one employee reports to 2 managers.
is there any way to implement this?
Thanks,
Haris
Hi Harris
there are two ways to solve that.
one: if it is only one other manager, another ManagerID field to be added to the employee’s table, like ManagerID2, and same process for that. the result to be a condition covering this and ManagerID.
two; if it might have multiple managers, then try the approach I mentioned here about many-to-many and organizational hierarchy
Cheers
Reza
Sorry to poke your notifier by leaving this comment, but these RLS articles are absolutely amazing ! Thanks ! 🙂
Thanks Remi for your kind words 🙂
This is really useful and helped me. can you please let me know if in case, few managers should able to see all the data(or all the employee data) like super user. How to achieve that, do we need to include that in RLS?
Hi Mona
You can add an Is Manager field, and change your DAX expression to consider that access level. here I explained about that method.
Cheers
Reza
Hi Reza. This is going to help my team a lot to set up secure access to our HR data. How would we amend the function to show 2 levels from any given manager? For example, if Lindsay was a manager Amy would see her reportees but Reza would not need to.
Hi Audrey
this is a good question 🙂 I might write about it in another blog.
what comes into my mind right now is that you need a way to find out what level this person is and only consider two-level below him/her.
this can be done using some string functions that count the number of (|) between, the reason is that we don’t really have a PathItemLevel function that tells us the level of the path item. you can also use the pathitem or pathitemreverse in the reverse order, but that might not be as efficient as parsing the text.
Cheers
Reza
Hi reza…I am implementing this strategy with the SSAS but it generating the following error:
The value ” in ‘GraphTestCUB'[DimSales.Manager_Id] must also exist in ‘GraphTestCUB'[DimSales.Userid]. Please add the missing data and try again.
this is because the user who do not have manager has its manager_id as blank/null. I am not able to fix this error despite seevral attempts.
If you can help.
Hi Asif. can you share a screenshot of your data model (relationships) and also the data in the table?
Cheers
Reza
There is no mechanism to upload screenshots.
I am exactly replicating your tutorial. It is working very fine when I direct access the table in PowerBI however when I implemented it SSAS cube and Dimension only then it fires the error. kindly provide your email I will send he screen shots or allow me to place a screenshots here.
There are some websites you can use to share a screenshot, like this: https://snipboard.io/
Cheers
Reza
Hi Reza. Thanks for the post.
What would it be the Dax code if you have the next situation…?
1. You have 10 sales executives and 2 group managers with 5 executives each one. Group 1 with executives 1 to 5 and Group 2 with executives 6 to 10, on January 1st. Each manager follows their own group sales.
2. The sales groups change from time to time (some executives are moved from one group to another in a slow changing dimension basis). For example: Executive 3 and 5 are moved into Group 2 and Executives 6 and 8 moved into Group 1 on July 1st.
3. Some executives leave the team and they are replaced with new ones.
4. The Group managers have a Commercial Manager that follows up all sales.
5. I need to create a dynamic RLS code to let every Group Manager to see the sales of his own team, but the team has changed during the present year, and could have changed during the last years.
Any help is greatly appreciated.
Hi Max
What you need is to tie up the organizational levels with profiles rather than users. The post here was a simple example of using users. If you use profiles, then the many-to-many relationship brings some complexity. I explained that in a two-part article here.
Cheers
Reza
Hi Reza, how would you hide the higher level columns dynamically if a lower level user login?
For instance, looking at your data ‘If Lindsay logged in then ‘Reza Rad’ and ‘Amy’ name shouldn’t be visible and if Amy logged in then she can see ‘Lindsay’ but not the Higher level ‘Reza Rad’.
How can we achieve this?
This is working as you mentioned already
a lower-level user cannot see anything from upper level
Cheers
Reza
Hi Reza,
I need to apply the ORG hierarchal level security on top of role based security, that is already applied on the sample powerbi file here
https://drive.google.com/file/d/1571cqkpl4qvZXX2Xs3omBak03m14i42x/view?usp=sharing
Kindly help me with this
Hi Raj
Check out my articles about many-to-many and organizational hierarchy RLS combined
Cheers
Reza