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

You would like to create a Power BI Report but not every user should see everything? Copying & sharing the report with individuals would be tough to maintain but not sure which other opportunities do you have? Row-Level-Security could be the answer!

In my first post I would like to demonstrate how easy a Row-Level-Security can be implemented.

First things first: I created a simple Excel Sheet which has different Regions, Managers, their internal mail and actuals for each region.

I open my Power BI Desktop and connect to the excel file.

In this demo I renamed my table inside Excel to “RowLevelSecurity” so I select it and hit load.

Now, I have connected to my Excel file and imported the data. As next step I would like to see which user is signed in to create a Row-Level-Security in a second step. I have now two options to do so:

1. Create a measure with the user name
2. Pass the function to call the user name directly in the role management

In my scenario I would like to create a measure. So I select “New Measure” in the Start Ribbon.

Now I can create a new Measure in which I can use DAX expressions. I find it also immediately in my table.

Because “Measure” is not an appropriate name I rename it to “User” and set it equal to USERPRINCIPALNAME. As soon as I start to type Power BI will recommend all functions which starts with “User”. After selecting USERPRINCIPALNAME I hit enter to save my measure.

To make sure I have the right function and the right user I check it by selecting my new measurement and use the “Card” Visual. Looks good so far.

Now I have to match my user with the Excel sheet and create a Row-Level-Security. To do so I hit “Manage Roles” in the Ribbon tab “Modeling”.

I can create a new role by hitting “Create”. I rename my new role to “Region Manager” and select the three dots at RowLevelSecurity. Here I can choose which field I would like to check with the current user. In my demo the user has to match with the “Account” field so I select it.

Last but no least I have to match the Account with my new created Measure “User”. To do so write [User] – it has to be in brackets – check and save it.

To check if my new created role works fine I hit “View as Roles” in the Modeling Ribbon.

A window pops up in which I can choose which role I would like to activate and also check what another user will see. I select Region Manager and hit OK.

To test it I create a new table visual with Region and Actuals and magic happens – it only shows my rows!

To make it 100% sure I also test another user. To do so I go back to “View as Roles” and test is as Yoda.

It works perfectly fine!

Thanks to Row-Level-Security I do not have to replicate my Power BI reports and share it with individuals to make sure who can see what!

In my next post I’ll show you how to set up RLS with a hierarchy.

Please let me know if my first 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