- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Many to Many Relationship - Row Level Security?
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?
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
If this post helps, please consider accept as solution to help other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
If this post helps, please consider accept as solution to help other members find it more quickly.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
06-06-2024 02:51 AM | |||
10-03-2024 06:42 AM | |||
08-29-2024 11:56 AM | |||
10-31-2024 10:14 PM | |||
08-26-2024 12:02 PM |
User | Count |
---|---|
134 | |
106 | |
88 | |
55 | |
46 |