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.

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?

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()

[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]
)