Many to Many Relationship - Row Level Security?

[Anonymous]

So I have two tables. A fact table with Territory ID's and then the Employee table has Empolyee ID's and Territory ID's.

The Employee table has a many to many relationship between Employee ID's and Territory ID's. So one employee ID will have multiple Territory ID's associated with it. Also, one Territory ID can have multiple employee ID's associated with it.

I was trying to think of a way to use this table to implement row level security on the fact table, but I am having trouble coming up with a way to do so. Lookupvalue would not work since there are multiple employee ID's for one territory ID. 

Is it possible to have row level security in this situation?

[v-shex-msft · Accepted solution]

Hi @Anonymous,

>>Is it possible to have row level security in this situation?

Many to many relationships not works on power bi.
For your scenario, you need to add a bridge table to link tables who has the multiple records.(modify 'many to many' relationship to 'many to one')
After these steps, you can enable RLS on this bridge table.

Regards,

Xiaoxin Sheng

[Dina]

Hey @v-shex-msft , how can I apply RLS to the bridge table? I have a similar scenario where I have one customer and many issuers (the security table) and then I have a dimension for the issuers. I created a bridge table of distinct issuers and I created a relationship with the dimension and the security table (both way filtering on both tables). When I applied RLS to the user security table it doesn't work.

Could you please elaborate more how do you apply RLS on the bridge table?

Thanks a lot!

[theschuss]

what is your grain of security - territory? Or would someone theoretically be secured to employees? 

If territory - I would make the security flow one way. If you have both, perhaps setup multiple roles and potentially create an interstitial table using a merge query with all combinations and secure through that? 

[Anonymous]

Not sure if I understand your question completely, but I believe the grain of security would be Employee ID. This employee ID would be tied with their email address so that when they view the report only the territories that are mapped to that emplyee ID would appear. 

[theschuss]

Ok, if you have security filter set to be bidirectional or one way from user and you should be fine if relationships exist between the tables. The user id will restrict the user table which will in turn limit the territory table through the inner join type logic.