In my first blog I showed how easy it is to set up RLS in general. This works perfectly fine if you’re working with a flat hierarchy. but what if it’s not flat? How to handle it? This post will show you how!

A common scenario is that a manager would like to see his and of his employees’ specific data like salary but of course the employees itself are only allowed to see their salary or from their employees below them in the org chart.

In my fictional organization Yoda is the top manager. Obi-Wan and Leia are on level 2 and each of them has two more employees (level 3). Yoda should be able to see his salary and every other as well. On the other side Obi-Wan has only the permission to see his and his employees’ salaries (Darth Vader and Luke Skywalker). Same goes for Leia. All level 3 employees mustn’t see level 1, 2, or from other people on their level. Therefore they are only allowed to see their own salary.

I created an example in Excel in which we have two tables. One (Table name Salary) with Employee ID, Manager ID and Salary. The other one (Table name Manager) with Employee ID, Employee Name and AD Account.

Now I open Power BI and connect to my Excel sheet. Once my table is loaded I check the relationship if the two tables “Salary” and “Manager” are correctly joined. Looks good so far.

Hint: If Power BI do not create a relationship automatically just create a new one by selecting Employee ID and drag and drop to the other field Employee ID.

My next step is to show the employees ID, employees name, salary, Manager ID and AD Account in a table to keep track if my data are shown correctly. Make sure employee ID and manager ID is not summarized.

My next step is to create a new column and add a so-called PATH function in my salary table. This function allows me to see the whole hierarchy path of each employee. I rename the column to “Hierarchy Path” and add the PATH function as follows: PATH(Salary[Employee ID], Salary[Manager ID]). As a result I get the whole hierarchy path for each employee separated by |.

My next step is to identify which user is logged in right now. To do so I add a new Measure in my Manager table and use the USERPRINCIPALNAME() function. My Measure is called User ID.

Because I do know now who is logged in I can match the AD Account and look which ID the user has. The function LOOKUPVALUE helps me to get the right information. Therefore I create another measure in my Salary table, call it Current User ID and use the following statement: LOOKUPVALUE(Salary[Employee ID], Manager[AD Account], [User ID]). To check if the correct user ID is taken I display my new measure in a table with the User ID. Yoda has the right Current User ID as we can see.

My final step is to add a new Role to manage the permission. I add a new one by selecting Manage Roles in the Modeling Ribbon, hit Create, rename it to Hierarchy and select the Salary table. At this point I add a new function called PATHCONTAINS which checks if something is inside a path – exactly what we are looking for because we have the whole hierarchy structure of each employee (function PATH) and we have also the current user ID (see LOOKUPVALUE). All we have to do now is to combine those bits of information like PATHCONTAINS([Hierarchy Path], [Current User ID]) = True.

My organizational hierarchy RLS is set up and should work! Let’s test is. As Yoda I should be able to see everything. I create a new table with Employee ID, Name and Salary. Afterwards I activate the Hierarchy role under View as Roles. Power BI notifies me that I’m viewing the Report in the role of Hierarchy. Nothing has changed so far because I can see everything as Yoda.

Now let’s change the user role. Right now I’m viewing my report as Yoda. Let’s try out Obi-Wan.

It works! Let’s test the last scenario with Darth Maul.

As we can see it works perfectly fine! With three simple functions I make sure that the permission is set up correctly and users can only see what they are allowed to see. Great!

In my next post I’ll show how to configure RLS properly in the Service.

Please let me know if this post was helpful and give me some feedback. Also feel free to contact me if you have any questions.

If you’re interested in the PBIX and Excel file used in this blog post check out my GitHub repo https://github.com/PBI-Guy/blog