Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Azure Analysis Service - Dual Role Security

Hey team,

 

Reaching out to see if I can get some perspective from users on the board who worked with RLS using Azure Analysis Services (or even SSAS on-prem) to define user group role security for roles with multiple conditions.

 

In my current use case, I'm working with an organization that wants to enable group security to access data from RLS through two dimension tables. One dimension wants to have data views restricted  by geography region, and the other dimension to have data views restricted by product type. We know currently that the two dimension tables are mutually exclusive and don't have any ID fields associated with each other. They do have a foreign key that are connected to the fact table via concatenation of two fields, but the Power BI report will have filters panels dedicated to region and product type.

 

I'm aware that in AAS, much like in SQL Server and Power BI Desktop, users can setup RLS and define the filters for a given role for any dimension table. In our use case there are currently more than 20 unique geography regions, and 2 product types. Right now the team leads want to limit the number of user groups (AAD groups) that they want to manage which may add more effort through this approach.

 

It looks like when performing RLS with one dedicated role for region only, and another dedicated role for product type, combining those two exclusive roles together allows users to see information that they shouldn't be seeing. However, when defining the DAX expression for both dimensions in one combined role (Geography+Product Type Role) in AAS as well as in Power BI Desktop, that solves the issue when uploaded in Power BI Service. In the grand scheme of things, it looks like that means security team will have to create at least 40 unique AAD group roles to maintain data access for users that will belong to each role. Where ideally we would want to go with 20 unique global roles and add one of the two product type roles to give to a respective user or AAD group. We thought about creating a unique ID for region and another for product type. But once they're grouped together and tied into a dynamic RLS setup, a group's credentials would have to be tied into a region-product type identifier regardless. I hope I'm not overthinking this.

 

Question for the community--is there any way to limit the number of groups created for this type of use case and bind two roles to one user or group without losing RLS? Or does Power BI currently only support RLS for a role with predefined multiple conditions if users want to filter this kind of report through those two fields?

 

Feel free to reply if there needs to be more context added. Thanks!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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!

View solution in original post

3 REPLIES 3
v-yiruan-msft
Community Support
Community Support

Hi @Anonymous ,

Could you please provide some sample data and screen shots(exclude sensitive data)? And please describe how it works for these roles(region and product type)?

Best Regards

Rena

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Hey @v-yiruan-msft ,

 

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 NumberCountryProduct TypeNet PriceNet Sale
1A150100
6A2100300
7A140500
2B240400
9B120200
3C160500
4C280200
10C270300
5D210600
8D280800

 

 

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 NumberCountryProduct TypeNet PriceNet Sale
1A150100
6A2100300
7A140500

 

 

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 NumberCountryProduct TypeNet PriceNet Sale
1A150100
6A2100300
7A140500
2B240400
9B120200
3C160500

 

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 NumberCountryProduct TypeNet PriceNet Sale
1A150100
6A2100300
7A140500

 

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!

 

Anonymous
Not applicable

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!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors