Dynamic RLS for users based on a column value with non-unique values

[Adam01]

Hello everyone, I'm trying to implement a holiday tracking PowerBI Report that has a dimension table with all the staff names and their respective department, please see below for an example:

I was wondering if there was any DAX I can use in the RLS Security Role Editor to dynamically filter the report to the department based on the user that is accessing the report. So say if Staff Member A was the view the report, all the visuals would be dynamically filtered to be Department 1 instead of just for Staff Member A. Similar to if Staff Member H was to view the report, the visuals would be filtered to be Department 5

It needs to be set up this way so everyone in a single department can view other staff members holiday within that department, however users should not be able to see holidays cross-department.

The only workaround I can currently think of is to create a Security Role PER Deparment and add each user manually to each security role (please see below), however we have an exponentially increasing Staff Dimension table so this way will result in alot of manual overhead from our administrator.

It is also worth noting that within the staff table of the report we store an email adress in the idential format to what the USERPRINCIPALNAME() function outputs. So in an ideal world I would have 1 Security Role we can add people to, the report would then extract the value for whichever department is assigned to that staff member based on USERPRINCIPALNAME(), and then filter the report to whatever department that staff member is assigned to

Any help is much appreciated

[Adam01]

Hello @AlanFredes

Apologies for taking ages to respond to your comment but thank you, this is exactly what I was after!

[AlanFredes]

Happy to help!

[AlanFredes · Accepted solution]

Hi Adam01,

You are almost there with the RLS setup.
What you need is the ability to filter a table by the User that then filters another table (through a relationship) to identify which department they belong to. This last table would then be used to filter all other data (using relationships).

Something like this:

This allows for the following RLS setup to be made:

RLS Role definition used:

You can even merge the RLS table and the Staff Department table into one and apply RLS on it.

Here is the Link to the file I used: Dynamic RLS for users based on a column value with non-unique values.pbix

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