RLS security

I have 3 tables:

1. access - this table holds userid and access_id. each user has one row for each access_id he has access to. access_id is the foreign key for the tables t1 and t2
2. t1 - this table holds objects that the user can have access to
3. t2 - this table holds objects that the user can have access to

I need to have the security filter on the userid field in the access table. This way, when a user logs in this will filter only the rows in the access table for that user, and the rows in tables t1 and t2 will only show the rows that the user has access to.

the t1 and t2 tables will have unique values in the access_id field.

the access table will have duplicate values in the access_id. But when we filter on a user the values in access_id is unique.

when creating the relationship i powerbi, he want's to create a one-to-many relationship from t1-to-access. In the filter direction field it's only possible to select single or both. I can't find any way to say that the filter direction should go from access-to-t1.

The model looks like below. This seems like a simple thing, but for some reason it's impossible to filter from many-to-one side.

One workaround is to select both in cross-filter direction and apply security filter in both directions. But this doesn't work when you have multiple tables connected to the access table.

Second workaround is to create the relationships as many-to-many. Then you can choose to filter from access-to-t1. But not sure if this will filter correctly when adding more tables.

Thanks for any suggestions 🙂