How to Handle Employee Hierarchy in Power BI

In this blog we will try to understand how to handle employee hierarchy and we will also learn about how to implement Row Level Security.

Let us first understand what is employee hierarchy or organization hierarchy. 

The level of hierarchy can be 3 or 4 or 5 etc. depending on the employee strength and company policy. Looking at the image above we can understand that :

1.  it depicts a 3 level hierarchy
2.  A is the top most employee in the hierarchy, may be we say he/she is CEO
3.  B and C reports to A and D,E and F reports to B.

So organization hierarchy basically determines 

1. who is reporting to whom
2. what is the level of hierarchy
3. who is the top most person
4. how many employees reports to a manager

Now we have a basic understanding of organizational hierarchy so lets move to the 2nd important aspect that we are going to learn i.e. Row Level Security (RLS). 

RLS is implemented so that each employee get to see only and only the relevant information.

For example :

Consider the above image depicts the organizational hierarchy of sales team. 

D is in charge of Pune region, generated revenue of 40 million $.

E is in charge of Mumbai region, generated revenue of 20 million $ and 

F is in charge of Delhi region, generated revenue of 30 million $

Now if we create a sales report depicting the total sales (which is 90 million $) and share the report with employees D,E,F and B then in the report

1. D should only see data related to Pune region i.e. total sales of 40 million $
2. E should only see data related to Mumbai region i.e. total sales of 20 million $
3. F should only see data related to Delhi region i.e. total sales of 30 million $
4. but B, as a manager of D,E and F, should be able to see all the sales data (of all the 3 regions) i.e. total sales of 90 million $.

RLS works at row level of a table, now consider that the table structure of the sales data is as follows

Employee	Region	Sales Amount (in millions, $)	Email ID	Manager
D	Pune	40	d@essenceoflearning.in	B
E	Mumbai	20	e@essenceoflearning.in	B
F	Delhi	30	f@essenceoflearning.in	B

Now since the sales data is given as per the employee, we can apply a security rule on the row such that the employees will be able to see the respective sales data only but the manager will be able to see the data for all the three. This rule is termed as Row Level Security.

What we want to achieve : 

We will create a report as shown below with 3 slicers L1, L2, and L3 and total sales measure shown in the middle

 

Now that we have understood what it RLS, now lets understand how to handle employee hierarchy for the following dataset.

1) Employee table

2) Sales table

We have 2 tables, Employee table contains the reporting hierarchy and Sales table contains the sales done by the respective employee.

Scenario : Each employee should be able to see the sales data relevant to him, i.e. 

• a manager would be able to see his sales data and also the sales done by his reportees at all hierarchy level
• a reportee, who is not a manager, should be able to see only his sales data.  

Before proceeding with the actual solution lets first look at the broader steps :

1. Join the 2 table so that we have only one table to deal with, if you don't want to join that is also fine, you can create link between the 2 tables in modelling section.
2. Create 3 new columns containing email id for the employees in L1, L2 and L3 columns, we would need the email id to apply RLS.
3. Create a measure to handle the total sales calculation
4. Implement RLS on the employee table

Join employee table and sales table :

• Click on Transformation Data
• Under Home tab click on Merge Queries
• Select the Sales table and click on Employee column, it serves as the point of join, for both the tables
• click on join and select only the Sales (in Million) column. Now the table will look like below

 

Create 3 new columns :

• In power query editor, select the Add Column tab
• Select both L3 and Email ID column in employee table (for selecting both the columns press ctrl key on the keyboard and then select the columns one by one by mouse key) and click on Column From Examples
• Now as per the names in L3 column write the respective email id of the employees, after some entries power bi engine would be able to understand, what you are trying to fill the column with and will populate the rest of the entries for you. In this scenario power bi was able to anticipate the content after 3 entries, but it all depends on the no. of rows and no. of columns you selected for the example
• Name the column as L3 Email ID and similarly create columns L2 Email ID and L1 Email ID.
• Right click on the Sales table and uncheck the Enable Load option, so that you don't see the table outside the power query editor. Click on Close and apply to save the changes 

Creating Total Sales measure : 

• SELECTEDVALUE() function is to identify what is selected in the slicer
• SUM() function is to add the total sales amount
• FILTER() function removes the irrelevant rows of the table
• CALCULATE() function provides the filtered data to SUM() function
• Since we have 3 slicers, we will have to use IF Else condition to check which slicer has the selection, following is the DAX expression used 

Total Sales = 
IF(SELECTEDVALUE('Employee Tbl'[L1],0) <> 0,
CALCULATE(SUM('Employee Tbl'[Sales Tbl.Sales (in Millions)]),FILTER('Employee Tbl', 'Employee Tbl'[L1]=SELECTEDVALUE('Employee Tbl'[L1]))),
IF(SELECTEDVALUE('Employee Tbl'[L2],0) <> 0,
CALCULATE(SUM('Employee Tbl'[Sales Tbl.Sales (in Millions)]),FILTER('Employee Tbl', 'Employee Tbl'[L2]=SELECTEDVALUE('Employee Tbl'[L2]))),
IF(SELECTEDVALUE('Employee Tbl'[L3],0) <> 0,
CALCULATE(SUM('Employee Tbl'[Sales Tbl.Sales (in Millions)]),FILTER('Employee Tbl', 'Employee Tbl'[L3]=SELECTEDVALUE('Employee Tbl'[L3]))),
SUM('Employee Tbl'[Sales Tbl.Sales (in Millions)])))) 
Implementing RLS :
Click on Modeling -> Manage Roles
Create a role as shown in the following image
Write the following code in the DAX expression area
IF(LOOKUPVALUE('Employee Tbl'[L1 Email ID], 'Employee Tbl'[L1 Email ID], USERPRINCIPALNAME()) <> BLANK(), 'Employee Tbl'[L1 Email ID] = USERPRINCIPALNAME(),
IF(LOOKUPVALUE('Employee Tbl'[L2 Email ID], 'Employee Tbl'[L2 Email ID], USERPRINCIPALNAME()) <> BLANK(), 'Employee Tbl'[L2 Email ID] = USERPRINCIPALNAME(),
IF(LOOKUPVALUE('Employee Tbl'[L3 Email ID], 'Employee Tbl'[L3 Email ID], USERPRINCIPALNAME()) <> BLANK(), 'Employee Tbl'[L3 Email ID] = USERPRINCIPALNAME(),True))) 
USERPRINCIPALNAME() function is to retrieve the email id of the current user
LOOKUPVALUE() function, is similar to vlookup function in excel, helps us to check if the current user's email id is present in the L1 Email ID, L2 Email ID or L3 Email ID column.
The written expression should always return a output as true or false, hence we have written the expression as  'Employee Tbl'[L1 Email ID] = USERPRINCIPALNAME(). Rows containing the email id will be present in the data and other rows will be filtered out.
Click on the Save button and we are done.
To verify if every thing is working fine, click on the "view as" option in the Modeling tab, select the "other user" and "manager role" options and mention the email id of an employee for which you want to verify the data, as shown in the image below.
 
We have completed all the steps and the report is ready now. I hope the blog will be of some help.