RLS based on user's department - of which they can have multiple

[PetyrBaelish]

I am trying to confiugre Row Level Security - I nearly had it working, but fell into the trap that the USERNAME() function can't be used in a calculated table.

My data is as follows:

Exam Results Table:

User - Result

Dan - 70%

Jane - 85%

Joe - 60%

HR Table:

User - DeptName

Dan - Finance

Dan - HR

Jane - Finance

Joe - IT

Note Dan works in two departments and therefore has 2 rows in the HR table. There is a 1 to many relationship (1 exam result to many in HR table) with bi directional filtering.

I created the following calculated table to only show the current user's departments (initially hardcoding a username - which worked fine)

Current User Departments = SUMMARIZE(CALCULATETABLE('HR Data','HR Data'[User] = USERNAME()),'HR Data'[DeptName],'HR Data'[User])

I then created a calculated column in HR Data:

Current User Check = IF(LOOKUPVALUE('Current User Departments'[DeptName],'Current User Departments'[DeptName],'HR Data'[DeptName])<>"",1,0)

My Role is defined as:

'HR Data'[Current user check] = 1

Whatever I've read online hasn't worked, mainly because it doesn't have the complication that a user can be in multiple departments

Does anybody have any ideas? I've tried to recreate this entirely in the DAX expression for the role but I've been unsuccessful - I got error messages creating a table variable due to 'base table is expected'.

[v-xuding-msft]

Hi @PetyrBaelish ,

I created a measure to calculate the DeptName. And put it into HR table visual, it will show all the DeptName. If we put it into card visualt, it will just show the max value of it .

Measure = CALCULATE(MAX('HR Data'[DeptName]),FILTER('Table','Table'[EMAIL] = USERNAME()))

The "Table" is my new table which contains user name and emails. 

Is this what you want?

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PetyrBaelish · Accepted solution]

I managed to get this working.

My HR Table as originally described became the "User Access" table, unrelated to other tables

I then have a HR Data table which contains 1 row for each user.

I used the following RLS expression on the HR Data table:

[DeptName] IN SELECTCOLUMNS(FILTER('User Access','User Access'[Username] = USERPRINCIPALNAME()),"Department",'User Access'[Department])

I had help mfrom here: https://radacad.com/dynamic-row-level-security-with-profiles-and-users-in-power-bi