The aim of this post is to show a ready-to-use RLS pattern that automatically manages access to Power BI reports according to a company hierarchy. Let’s take a standard tree-shape hierarchy example, in which John is managing director, Matt is head of sales, and Sebastien a regional sales manager with a team of three reps, as shown below:

When John connects to the reports, he will see all sales data and KPIs, with the ability to drill into each member of the hierarchy’s numbers. On the other hand, Sebastien will only have access to his team’s sales report and have no visibility on Paul’s figures, for example.

Let’s build a RLS following two steps: one calculated column and one measure that defines the role of each person.

Employees table query

Keep it very simple: assign a manager to each employee, where applicable. Translated into table, data is presented as follow:

John (A001) is the top manager so “Manager” field is blank on his row. The employee table is linked to the sales fact table as follows:

Get dynamic RLS in two steps

The first step is to get the hierarchy in the table using path function in a DAX calculated column of the employees table:

Hierarchy = PATH(EmployeeTable[Employee ID], EmployeeTable[Manager])

The second step implies a bit more manipulation. Basically, respecting the order that the Power BI Service will process, we want it to understand 1) who is connected, 2) return their employee ID, and finally 3) show only the rows where their ID appears in the Hierarchy field. The solution is the following DAX expression in the Manage roles editor from Power BI Desktop, linked to the employee table:

With:

1. USERPRINCIPALNAME() function. Say Matt is connected, matt@keymetrics.com will be returned
2. LOOKUPVALUE() function will return his employee ID “A002” as we make it match with his email address, and
3. PATHCONTAINS() function will return TRUE for every row where Hierarchy field contains his “A002” ID.

As a result, we get the employee table filtered on people who are managed by Matt and he will see the reports of employees that he manages only. Let’s look at an example. For that, go on View as roles in the Modeling ribbon, and enter John’s email address.

You will see that if John connects to his report, he will see all employee figures, as follows:

Now, if you do the same view as roles with Matt, you will only see the figures of employees that he is responsible for:

Thanks for reading!

Modrek