HR Analysis – How to Mask Salaries (and a Parent/Child Hierarchy Power BI Tutorial)

HR Reporting is a sensitive topic where we often have to implement a variety of rules and constraints. Row Level Security is one of the options that we often use to implement those rules, but sometimes, the filtering behavior of RLS is not exactly what we want. In our scenario, imagine a situation where I and my direct reports would like to go over the salaries in my department. If I am a CEO, then I do not want the VPs who report to me see each other salaries while all of us are looking at this report in my office. So, I would like to mask the salaries of my immediate direct reports but display the salaries of their direct reports and so forth. The reason I would like to mask them and not filter them out is so that I can still show the departmental/company total while masking certain details when looking at the individual employees.

While we are at it, we are taking a deep dive into the PATH…() functions so we can get a better grasp on how to work with parent child hierarchies in Power BI.

You can download the Power BI Desktop model used in this tutorial here.

Masked Salary = 
var currentManagerID = SELECTEDVALUE(Managers[EmployeeID])
var currentEmployeeID = SELECTEDVALUE(Salaries[EmployeeID])
var currentEmployeePath = SELECTEDVALUE(Salaries[Path])

var canSeeEmployee =
    PATHCONTAINS(currentEmployeePath, currentManagerID) &&
    SEARCH(CONVERT(currentManagerID, STRING), currentEmployeePath, 1, 0) < 
    SEARCH(CONVERT(currentEmployeeID, STRING), currentEmployeePath, 1, 0)

var isDirectReport = (currentManagerID = SELECTEDVALUE(Salaries[ManagerID]))

return
IF(
   canSeeEmployee,
   IF(
       isDirectReport,
       "*",
       SELECTEDVALUE(Salaries[Salary])
   )
)