Row level security - column value starts with value from logged in user

[rmarian]

Hello,

I am trying to set up row-level security on a table that contains unique list of all employees. Available columns:

• Employee email address
• RBS (resource breakdown structure) - shows the employee position in the organization in a format like this: "Division.Unit.Department.Team.etc..." (it can have as many levels as needed)

The goal is that logged in user (line manager) should only see data for employees on the same RBS level as he is, or on levels below.

For example unit head with RBS "Division.Unit" should see all employees with RBS

• Division.Unit
• Division.Unit.Department1
• Division.Unit.Department2
• Division.Unit.Department1.Team1 etc.

They should not see emplyees with RBS

• Division
• Division.Different Unit
• Division.Different Unit.Department etc.

In the DAX formula, I need to call USERPRINCIPALNAME() to get the email address of the logged in user, but how do I check which rows' RBS starts with the logged in user's RBS?

Any help will be very appreciated!

[rmarian · Accepted solution]

Thank you for your suggestions. While they might work, I was not able to easily implement them.

What I did instead was set the RLS to filter all employees where the RBS contains the RBS of the logged in user, and then exclude the logged in user.

IF(Employees[Employee Email]=userprincipalname(),False,CONTAINSSTRING(Employees[RBS],LOOKUPVALUE(Employees[RBS],Employees[Employee Email],userprincipalname())))

[Greg_Deckler]

@rmarian Use the PATH set of DAX functions. If you replace your periods with "|" character that would make it a PATH for DAX. Alternatively, split your column out based upon "." character.

[Greg_Deckler]

@rmarian - Dynamic Hierarchical RLS - https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Hierarchical-Row-Level-Security/m-p/...

[rmarian]

@Greg_Deckler  Could you please elaborate how to apply this to my data? I don't have a direct child id-parent id connection between the rows, I can only get this information from the RBS column which is type string. How do I convert to path?

[Rigensis]

@rmarian Basically you can do it, but you would need to create another column in your users table, that would contain all users that can access the particular users data.

For example,

user that has RBS of Division.Unit would only need his own email in the new column,

while a user that has RBS of Division.Unit.Department1, in the new column would have not only his own email, but also the email of the first user mentioned, as that one can also access this lower level.

basically you would have to recreate RBS column, but with user emails. 

The highest level user would only have his own email in the new column, effectively only he can see the data that is connected with his RBS.

while the lowest level user in the new column would have emails of all the people that are higher up in the same RBS chain as he is, effectively giving all of them access to what he sees as well.