Power BI RLS with organization hierarchy and hide other lines

[IHOUM]

Hello,

I would like to implement dynamic Row-Level Security (RLS) based on our organizational hierarchy. I want managers to be able to view the data pertaining to members of their team directly. For other entries, they should appear as "XXXX" alongside the associated values, as illustrated in the picture below.

I don't know how to display the other lines like this, any thoughts ? 

[Bipin-Lala · Accepted solution]

Hi @IHOUM,

Since you haven't shared what your Power BI Model looks like or what relationships exist between various tables and fields, I am assuming that there must be table mapping managers with their team members.

Assuming you implement RLS correctly, then yes, a manager would be able to see the names of their team members, but other employees will get filtered out. Hence you won't be able to display entries with 'XXXXX' and corresponding Nb days, since the data should ideally get filtered out for the specific manager.

If you want to display the mask the employees who are not part of the manager's team, I would suggest creating a calculated measure/ column that would check if the members belong to logged in manager's team, otherwise display 'XXXXX'

Display Name = 
IF(
   'Hierarchy'[Manager] = USERNAME(),  -- Check if logged-in user is manager
   'Employee'[Name],                   -- Display actual name if manager
   "XXXX"                               -- Display "XXXX" otherwise
)

With the above DAX, you should be able to mask users that a manager should not be able to view, otherwise they should be able to view the complete data.

Let me know if the above fulfills your requirements. If it doesn't, can you please share some more details regarding your question, like table structure, and relationships, or maybe share the pbix file? RLS is suitable in this scenario if you don't want managers to view any data about team members that do not belong to their team, however in your case, we are not filtering the data, but rather masking the data.

All the best!'🙂

[IHOUM]

Hi @Bipin-Lala ,

Thank you for your solution. 

It is working really well with a measure but calculated column doesn't support dyanmic fields like USERNAME().

[Bipin-Lala]

Hi @IHOUM,

Ahh, yes, that's absolutely correct. Calculated Columns are computed only once when the model is loaded/ dataset is refreshed and aren't dynamic as measures, and thus functions like USERNAME() and USERPRINCIPALNAME() will work with measures only. My bad!

Hope the solution is working as per your expectations! All the best!