Dynamic Row Level Security Based On Multiple Fields

[alindquist]

I have a dataset that looks like this:

Each transaction has a territory code and a bill to code, and each of those codes has a user associated. In this example, territory or bill to code NAP1 is associated with user 1, territory or bill to code NAP2 is associated with user 2, and territory or bill to code is associated with user 3.

I need to implement dynamic row level security as follows:

• If the territory code OR the bill to code begins with "NAP", any associated user should have access to the data
• If the territory code OR the bill to code begins with anything else, the user associated with that code should have access to the data

In this example, users 1 and 2 both are associated with codes beginning with NAP so they should both see all rows where territory or bill to code begins with NAP. Or in other words, they should both see all transactions except 9 and 12. User 3 should only see transactions 3, 7, 9, 10, 11, and 12.

I created a sample .pbix file that you can download here.

[lbendlin]

When you say dynamic RLS do you mean dynamic RLS as in "based on USERPRINCIPALNAME mapping"  or do you mean "roles per user with rules that include OR statements"  ?

[alindquist]

@lbendlin I need to use USERPRINCIPALNAME() to base the security on the user who is logged in. The rule also needs to consider both the territory code and the bill to code, so it will probably require an OR statement.

[lbendlin]

user = USERPRINCIPALNAME() || Left([Territory Code]="NAP" || Left ([bill to code]="NAP"

[alindquist]

I couldn't get that to work. However, I was able to figure out a solution that works. First I added two custom columns to the data model like this:

TerritoryRLSCode = if Text.StartsWith([Territory Code], "NAP") then Text.Start([Territory Code], 3) else [Territory Code]

BillToRLSCode = if Text.StartsWith([Bill To Code], "NAP") then Text.Start([Bill To Code], 3) else [Bill To Code]

Then I created a new role with the following logic:

OR(
'Table'[TerritoryRLSCode] IN SELECTCOLUMNS(
FILTER('Table', 'Table'[Territory Email] = USERPRINCIPALNAME()),
"TerritoryRLSCode",
[TerritoryRLSCode]
),
'Table'[BillToRLSCode] IN SELECTCOLUMNS(
FILTER('Table', 'Table'[Bill To Email] = USERPRINCIPALNAME()),
"BillToRLSCode",
[BillToRLSCode]
))

I'm sure there's a better way to accomplish this, but it does work as expected. Here's a link to the .pbix of my solution