RLS with Multiple Role Purposes

[m-colbert]

I have a model (simplified version) that has two dimensions A and B and one fact able.

My scenario is that I have a set of roles for each dimension. A user will belong to one of the A roles and one of the B roles. Each set has an All role where no filtering should take place, and that DAX filter is set to true().

Dimension A is filtered by a user belonging to one of 4 roles, Role A1, Role A2, Role A3 and Role A-All.  The DAX filter on dimension A for each role is based on a single column in dimension A, i.e. [columnA] in {Value1, Value2, ..., Value(n)}.

Dimension BA is filtered by a user belonging to one of 3 roles, Role B1, Role B2, and Role B-All.  The DAX filter on dimension B for each role is based on a single column in dimension A, i.e. [columnA] = Value1.

I created a single table visual with a column from dimA and dimB and one measure from the fact table.

When I View As, and select a single role from dimension A, the fact table is filtered coorectly. Likewise, if I select a single role from dimension B, I get the expected results. However, if I select a role for dimension A, such as A1 and a role from dimension B, i.e B2. I would expect that the results would be the combination of the rows filtered from A and the rows filtered from B both applied. Just like if I used a slicer on A and a slicer on B.

The actual results are an odd combination, not all of either A or B but a combination I can't quite determine. Am I misunderstanding how this combination should be applied? I did as a test create a new role that had dim A and dim B table filtered (i.e a DAX filter associated with both tables in the role definition) and this works correctly. However, I don't want to create a role for every combination of A and B filters as this can grow can quickly get out of hand to manage.

Thanks

[Anonymous]

Hi @m-colbert ,

Based on the test the result in this case should be the union of role A and role B. 

After further digging, I found the answer to this question from the documentation: 
//When a report user is assigned to multiple roles, RLS filters become additive. It means report users can see table rows that represent the union of those filters.

This description is consistent with our description and test results, so this should be how he works.

The document also described best practices when creating roles:

//When you're considering the permission needs for a single report user, strive to create a single role that grants all those permissions, instead of a design where a report user will be a member of multiple roles. It's because a report user could map to multiple roles, either directly by using their user account or indirectly by security group membership. Multiple role mappings can result in unexpected outcomes.

Therefore, you need to choose between creating fewer roles or achieving the desired effect.😞

For your reference: Row-level security (RLS) guidance in Power BI Desktop

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

[m-colbert · Accepted solution]

@Anonymous 

The documentation may indicate that they are additive, whch was also my assumption. But I can assure you the results are not consistent with that behavior. 

I implemented the dimension A as a individual roles and then added the same DAX to filter dimension B to each role and used dynamic RLS with associated user/user profile tables in order to combine them correctly. This is a very limiting implementation of roles and accurately combining roles should be possible. There was no overlap of DAX filters between roles also it should work.