Power BI Tutorial: Row Level Security on Parent Child Hierarchy

EDIT: I recorded a detailed video on how to work with Parent Child hierarchies in Power BI: https://businessintelligist.com/?p=2603

In the last post, I talked about flattening parent child hierarchy. Building upon that – the next question is how to make sure that an employee has access to only his/her data and that of the employee(s) reporting to them. This can be achieved using Roles in Power BI.

In the previous post, we created four levels using PATH an PATHITEM functions.

• Let’s add another column to Employee table to get the Manager’s email address.
  Manager Email = LOOKUPVALUE(Employee[Email],Employee[First Name],Employee[Manager])
• Now let’s add one more column using the PATH function. This time we will use email address field. This gives us the flattened hierarchy using email address. We are going to use this field to add security.
  Email Path = PATH(‘Employee'[Email],Employee[Manager Email])

• Add a new role using the following DAX
  IFERROR(SEARCH(USERNAME(),[Email Path]),-1)>0

This looks for the Username (email address of the person logged in) in the Email Path column. If it is found, then the user has access to that record.

You can download the solution from here.