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