Power BI Tutorial: Row Level Security on Parent Child Hierarchy

No comments

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.

Hierarchy 2

  • 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])

Hierarchy 3

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

Hierarchy 4

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s