Search code examples
powerbidaxrow-level-security

Setting Row level security with multiple columns in Power BI

I am trying to create a filter for target table having country code column. I want to give access to people with logged in upn either in AdditionalOwner or OwnerEmail with countrycode

AdditionalOwner has emails separated by commas, Number of emails in additionalowner column is not fixed also it may have values from OwnerEmail column. So Please help me in giving RLS in this scenario

Please find my column structure

CountryCode AdditionalOwner OwnerEmail
AU test1@test.com,test2@test.com test2@test.com

Here is the DAX I have used for Owner Email

[Country Code] = LOOKUPVALUE(
UserRoles[CountryCode],
UserRoles[OwnerEmail],UserPrincipalName())

Please help me in adding RLS for AdditionalOwner column too

Solution

  • You may want to try using something like OR, IF, and CONTAINSSTRING in your managed role. I've included an example for you to test below. For transparency, I did not test this but this is where I would start to solve this.

    OR (
 UserRoles[CountryCode] = 
    IF(    
       CONTAINSSTRING ( UserRoles[AdditionalOwner], UserPrincipalName() )
      , UserRoles[CountryCode], BLANK() )
 , UserRoles[CountryCode] = LOOKUPVALUE(
 UserRoles[CountryCode],
 UserRoles[OwnerEmail], UserPrincipalName())
)