Transform static RLS to dynamic RLS dealing with many to many relations

[SebV]

Hi,

I try to make a static RLS dynamic, but can't sort out the DAX needed.
The datamodel has at table for users and each ueser is linked to a single departmemnt.
Each departmant is entitle to access certain area's, some om on the level of Counties, some on the level of Districts.

In the original situation static RLS defined the specific counties and districts for each department. Due to increasing number of roles there is a wish to make this dynamic.

One of the problems is that the filter direction in the one to many relationship between Users and Departments is from Departments to Users. Therefore restrictin the User table with E-mail == USERPRINCIPALNAME() won't filter down the Departments table.

I tried different approaches to solve this:

Approach 1: the combination of DAX in RLS:
Table Users: [E-mail] == USERPRINCIPALNAME()
Table Departments: [Department] IN VALUES(Users[Department])
Table Departments - Access shoudl be filtered due to its relationship with table Departments
Table Areas: [County-id] IN( VALUES('Department - access areas'[Access area])) || [District-id] IN( VALUES('Department - access areas'[Access area]))

Unfortunately, this doesn't work.

Approach 2:
Restrict table Departments by: [Department] == LOOKUPVALUE(Users[Department], Users[E-mail], USERPRINCIPALNAME())
Table Areas: [County-id] IN( VALUES('Department - access areas'[Access area])) || [District-id] IN( VALUES('Department - access areas'[Access area]))

Doesn't work either

Approach 3:

Restrict table Departments by: [Department] == LOOKUPVALUE(Users[Department], Users[E-mail], USERPRINCIPALNAME())
Table Areas:
[County-id] IN( CALCULATETABLE( VALUES('Department - access areas'[Access area]), 'Department - access areas'[Department] = SELECTEDVALUE(Departments[Department]))) ||
[District-id] IN( CALCULATETABLE( VALUES('Department - access areas'[Access area]), 'Department - access areas'[Department] = SELECTEDVALUE(Departments[Department])))

Also doesn't work.

Anyone who has some valuable suggestions?

PBIX attached:

https://www.dropbox.com/scl/fo/a5fitksgp9hm4ycvf9gee/AG5QR3-1nKT8kaoPuBL1p7A?rlkey=ui12vthaw4f3mwdv2... 

[lbendlin]

That's not what dynamic RLS is about.  In Dynamic RLS you maintain a reference table that lists exactly which artifacts a user has access to. That reference table is controlled by USERPRINCIPALNAME, and in turn controls the rest of the data model.