Row Level Security conflict with business logic

[xred11]

Hi, New to Power BI and I hope someone can suggest a solution to this problem:

I have 3 tables (see below for sample) and need to implement row level security:

BRANCH (branchcode, branchname)

• 123, branch123
• 234, branch234
• 345, branch345

TRANSACTION (branchcode, type, [other stuff...])

• 123, 1, ...
• 123, 1, ...
• 345, 2, ...

USER_BRANCH (user, branchcode)

• abc@xyz.com, 123
• def@xyz.com, (blank)  (<< this is the problem)
• ghi@xyz.com, 345

BRANCH is on the "one" side of 1:* relationships in the model with the other 2

The RLS we want is that user should equal userprincipalname().  We have it working fine if we have a branchcode for the user ([user] = userprincipalname() or something like that), BUT

HERE'S THE PROBLEM:

The business logic used in the app states that if a user doesn't have a branchcode (they're blank) then they have access to ALL the branches.  These users would be in a home office that needs to see all transactions no matter what the branch.

The only solution I'm seeing is adding every combination of "blank" users for all the branches, but that will add 50,000 records and I am not excited about that.  Does anyone have another solution?

[xred11 · Accepted solution]

OK, I got it working without getting into groups assigned to multiple roles.

1. I turned off security filtering in both directions between BRANCH and USER_BRANCH
2. I added this filter to the role on BRANCH:
   OR(
   contains (USER_BRANCH, [branchcode], "", [user], userprinicalname())
   ,contains (USER_BRANCH, [branchcode], [branchcode], [user], userprinicalname())
   )

[xred11]

Would this work?  Put the "blank" users in a role/group that doesn't have the filter on that field?