Row Level Security DAX Expression not working with USERPRINCIPALNAME()

[wow921]

I'm trying to implement a row level security using the below code but it doesn't work as intended.

[dept] = MAXX(FILTER(email_mapping, CONTAINSSTRING(email_mapping[Permissions], USERPRINCIPALNAME())),email_mapping[Dept])

However, when I replaced USERPRINCIPALNAME() with the actual email address, the RLS filter works. Is there something wrong with my DAX expression which does not allow USERPRINCPALNAME() to work?

[dept] = MAXX(FILTER(email_mapping, CONTAINSSTRING(email_mapping[Permissions], "234@abc.com")),email_mapping[Dept])

Below is the email mapping table. This is a standalone master data and will have a one to many relationship to the main table which requires row level security filtering as per the USERPRINCIPALNAME()

Dept	Permissions
finance	123@abc.com; 234@abc.com; 345@abc.com
IT	3245@abc.com; 4567@abc.com

[Joe_Barry]

Hi @wow921 

If the Department column has a relationship with the Fact Table Department, then try this.

CONTAINSSTRING(email_mapping[Permissions], USERPRINCIPALNAME())

Joe

[wow921]

Thanks for the reply. Yes the Department column has a relationship with the Fact Table Department. The DAX Expression you have provided seems no different to mine? 

[Joe_Barry]

It is that in that there is no need to define the Department if you have a relationship between both tables.

I am presuming that you are using the Modeling > Manage Roles feature?

[aduguid · Accepted solution]

Try this DAX

[dept] = MAXX(
    FILTER(
        email_mapping, 
        CONTAINSSTRINGEXACT(TRIM(email_mapping[Permissions]), TRIM(USERPRINCIPALNAME()))
    ),
    email_mapping[Dept]
)