Repeating category breaks RLS

[ZR3036]

I got RLS to work in my report in desktop and service in a test run, in which my RLS looks at a linked table/xls with name/role/email.  The RLS in Modeling -> Manage Roles uses email == USERPRINCIPALNAME so that the email a viewer is logged into Service with will restrict their data views.  The name/role/email table had a 1:1 bidirectional relationship with a table that's only the roles for filtering, and everything was working correctly.

When I started dropping in the actual names/roles/emails to the linked table/xls, after refreshing it errored out due to the repeating values in roles (i.e., there are 15 people in Marketing who should be allowed to view the Marketing data).  I have swapped this to one:many and many:many but in both instances, when I go back to "View As," it no longer filters the data based on the RLS table and role associated with the email.  Why did it stop filtering and how do I fix it?

[v-xiandat-msft]

Hi @ZR3036 ,

The shift from 1:1 bi-directional relationships to one:many or many:many does affect the way RLS filters data. This is because RLS filters are propagated only through active relationships, and the nature of these relationships can significantly affect the way the filter is applied.

My suggestion is that you can set unique numbers for each employee and then use the numbers for RLS filtering to avoid duplicate values.

RLS_SecurityKey = CALCULATE(FIRSTNONBLANK(People[Group],People[Group]),     
    FILTER(ALL('PEOPLE'),USERPRINCIPALNAME() = People[Email]))

 Below is the post will help you:

powerbi - Using RLS on secondary field to UserPrincipalName - Stack Overflow

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ZR3036]

It ended up being as simple as making sure that the security filter was applied in bth directions for the 1:many relationship.  Unsure why it was fine with 1:1.