Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.