Forum Discussion
Azure Analysis Service - Dual Role Security
I was able to get my solution by talking with the Microsoft Power BI Support team. I opened up a ticket and got on a call with the engineers. By design, Power BI currently doesn't allow one role to filter out another role. The solution is that the DAX filters have to be applied to one single role to filter out the columns of interest, to create multiple conditions.
Example: To create a role for a user who wants to see data for Country B + Product Type 2
Instead of:
User in two separate roles where:
Country B Role = [Country] = "B" and Product Type 2 Role = [Product Type = 2, which will not work for my use case,
you would want to have the following below:
User Role for Country B+ Product Type 2 = [Country] = "B" && [Product Type] = 2
I hope this helps to anyone else who is curious in knowing whether or not dual role security is possible with separate DAX column filters.
Thanks all!
Hey Anonymous ,
Apologies for the minor delay.
I'll use a small example with mock data.
Below is an example table that I have with country and product type as the two columns we want to create security for:
| Order Number | Country | Product Type | Net Price | Net Sale |
| 1 | A | 1 | 50 | 100 |
| 6 | A | 2 | 100 | 300 |
| 7 | A | 1 | 40 | 500 |
| 2 | B | 2 | 40 | 400 |
| 9 | B | 1 | 20 | 200 |
| 3 | C | 1 | 60 | 500 |
| 4 | C | 2 | 80 | 200 |
| 10 | C | 2 | 70 | 300 |
| 5 | D | 2 | 10 | 600 |
| 8 | D | 2 | 80 | 800 |
What we want to achieve is to create individual roles dedicated to a given Country and Product Type. In this case, we would have 4 unique Country roles [A,B,C,D] and 2 unique Product Type roles [1.2].
If I were to view the report as a user who's given the role to see data for Country A, I would see the table below:
| Order Number | Country | Product Type | Net Price | Net Sale |
| 1 | A | 1 | 50 | 100 |
| 6 | A | 2 | 100 | 300 |
| 7 | A | 1 | 40 | 500 |
However, if I want to give an additional role to a user who wants to see data for Product Type 1 and Country A, they would be assigned to both of those roles through DAX Filters (via RLS). However when I view both roles together, this is what I would see instead, where the red font indicates that they should not be be seeing the following records:
| Order Number | Country | Product Type | Net Price | Net Sale |
| 1 | A | 1 | 50 | 100 |
| 6 | A | 2 | 100 | 300 |
| 7 | A | 1 | 40 | 500 |
| 2 | B | 2 | 40 | 400 |
| 9 | B | 1 | 20 | 200 |
| 3 | C | 1 | 60 | 500 |
Our goal is to limit as many roles where possible and have users belong to multiple roles. However it looks like assigning them to individual roles will give them access to data that they should not be seeing.
From here, the workaround is to create a unique role combination where Country = A and Product Type = 1, and we'll add an AD Security group into that role. Users will then roll into that group. Below is what the desired effect should be:
| Order Number | Country | Product Type | Net Price | Net Sale |
| 1 | A | 1 | 50 | 100 |
| 6 | A | 2 | 100 | 300 |
| 7 | A | 1 | 40 | 500 |
That would mean we would have to crate 8 role combinations to keep this desired effect instead of 6 roles. This adds more maintenance for the security team.
My challenge with this is if there is indeed a way to keep individual roles and have users be given two roles without allowing them to see unwanted data.
Let me know if you'd like me to elaborate more on this. Thanks!
I was able to get my solution by talking with the Microsoft Power BI Support team. I opened up a ticket and got on a call with the engineers. By design, Power BI currently doesn't allow one role to filter out another role. The solution is that the DAX filters have to be applied to one single role to filter out the columns of interest, to create multiple conditions.
Example: To create a role for a user who wants to see data for Country B + Product Type 2
Instead of:
User in two separate roles where:
Country B Role = [Country] = "B" and Product Type 2 Role = [Product Type = 2, which will not work for my use case,
you would want to have the following below:
User Role for Country B+ Product Type 2 = [Country] = "B" && [Product Type] = 2
I hope this helps to anyone else who is curious in knowing whether or not dual role security is possible with separate DAX column filters.
Thanks all!