RLS: Users with multiple roles filter problem

[JVidal]

Hi all, 

I have a model with Companies and Departments. My requirement is to create RLS for both Companies and Departments and assign them to users. Some users need to have both roles appliead at the same time, ie, they are not allowed to see the entire Company, but their department only.

While I can apply multiple roles to a user, the data displayed is not the intersection of these roles. What I can see is the addition of the roles.

As an example, a user with RLS: Company A + Department B will see all Company A + Department B for all companies, which is not what I need.

Is there a way to apply both roles restrictevly?

Thanks

[patyhelenl]

I have the exact same issue, I want the intersection of two groups. Did you found a solution?

[v-alq-msft]

Hi, @JVidal 

Basedon your description, I created data to reproduce your scenario.

Table:

If you create two roles as below, it won't display the intersection of two roles.

 

Result:

You need to put the rules in one role like below and then it will show the intersection of two rules.

Result:

Here is the pbix .

Best Regards

Allan

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

[JVidal]

Hi @v-alq-msft 

Thank you for taking the time to review and create the small example. 

I was also considering your approach, but since I have 20+ Companies and around 10 Departments, I would like to avoid as much as possible to create roles for all different possibilities of Company+Department. Another issue is that your approach needs to be applied at the fact table, where I would like to apply them at the dimension tables so the filters of the report get also filtered with relevant information. Applying to the fact table would require me to enable bi-directional filters and I would like to avoid it.

Being able to assign multiple roles to users is very helpful at avoiding this situation, as it will avoid a lot of work and maintenance in the future.

Thank you

Kind regards,

[philidor]

I have the same problem, and I'm trying to find the solution, let me know if you could solve it.

[parry2k]

@JVidal you can surely achieve any logic in RLS, I have written few blogs on RLS , not directly solution to your problem but it explains how RLS works and you can implement it in your model. You can check blogs at this link.

I would 💖 Kudos 🙂 if my solution helped. 

[JVidal]

Hi, 

Thanks for the link, I will have a look. Perhaps I should have mentioned that the roles come from different dimension tables. So I have Companies in one table and Departments in another table.

Kind regards,

[parry2k]

@JVidal sure here is the blog post that specific talks about use business rules to apply RLS.

[JVidal]

Hi @parry2k 

I have read your post and I suspect you have all your data in 1 table and then your measure works, but if your filtering criteria is in different tables, I am not sure how to implement the measure.

On the other hand, the example of your post is exactly what I want to avoid. I don't want users to see outside their company/department.

So I have 3 case scenarios:

- Users who can see the entire company -> No issues here (RLS to filter Company)

- Users who can see Department for all the companies -> No issues here (RLS to filter Department)

- Users assgined to a specific company and department -> Issue is here as the current setup of applying two RLS roles is not applying correctly, as it will show the entire company and Department for all companies. It looks like the filter is applied with an OR statement instead of and AND statement.