The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Here is the RLS scenario I am trying to find a solution for.
In our opportunities table, we have both the Account owner, and the Opportunity owner. The account owner is not always the same as the opportunity owner...and vice versa. Our management wants the salesperson to see their opportunities (and only theirs) if they are the account owner and/or the opportunity owner. So in the example below, Jane Smith should see Opportunities a, b, and d.
The challenge is that User IDs in our master employee table filters the Accounts table, (one user to many accounts), which in turn filters the Opportunities table (one account to many opps). So in my current setup, the RLS will only show users the opportunities that they are the Account owner of, since the Accounts table is filtering the Opps table.
In essence what I need is that if the userprinciplename = current user, and if that current user's USER ID matches either the account owner id OR the opp owner id, then show those records. Just not sure how to implement this.
Account Owner Opp Owner Opportunity
Jane Smith Jane Smith a
Ted Jones Jane Smith b
Jack Torres Jack Torres c
Jane Smith Ted Jones d
Ted Jones Jack Torres e
@otravers Interesting idea. Another approach I was thinking was to (in query editor) say: If account owner = opportunity owner, then true/false. Then apply RLS agains that column where the current user (user principal name) would see only the True values. Just need to figure out how to set that up in DAX or in the modeling view.
There are probably different ways to approach this. One option would be to unpivot the Account Owner and Opp Owner columns into two columns: Owner and an Owner Type, then apply RLS against the Owner column.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
58 | |
40 | |
30 | |
26 | |
23 |