cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rmarian
Frequent Visitor

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

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!

1 ACCEPTED SOLUTION
rmarian
Frequent Visitor

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())))

 

View solution in original post

5 REPLIES 5
rmarian
Frequent Visitor

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())))

 

View solution in original post

Greg_Deckler
Super User IV
Super User IV

@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.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Greg_Deckler
Super User IV
Super User IV

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

 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




@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?

@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.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.