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?

[Ray_Minds]

Here we have multiple scenario as mentioned in the below :
1. If you have star schema model like your "RLS table" directly connect to fact table that means your expression has been worked perfectly with one to many relationship at single direction.

2. If you have snow flake model like ((RLS table ) 1---->--many(Usertable) 1---->----many(fact table)) that means you will need follow these step as mentioned below:-
a). you will need to select "both" in the cross filter direction and "tick the apply the security on both direction" between (Usertable) 1--<-->----many(fact table). I hope this should be helpful.

3. If these two thing not matched your scenario , please provide some sample. so that we would be help you.

[Anonymous]

Hi @ZR3036 ,

Has your problem been solved? If so, please mark the correct answer.

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.

[Anonymous]

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.

[Anonymous]

Hi @ZR3036 ，

One-to-one relationship ensures that the unique IDs of both parties are linked to each other and the data will not be misplaced.

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.