Dynamic RLS base on several columns

[Tracy000]

Hello team,

I got a problem with dynamic RLS which need your kind advise, thanks in advance.

I have a fact table and several dimension tables, they joined with 1(dim table) to many(fact table).

Now I want to setup RLS base on requirement, we have many different roles, even for one user, his/her role base on several columns as following:

"Product" table:
column: business group(values: All_product, PC, Monitor),   business type (values: All_Wty_Svc, Wty, Svc)
All_product includes both PC and Monitor, All_Wty_Svc includes both Wty and Svc

"GEO" table:
column: region (values: WW, AP, AM, EMEA)
WW means user have authority to see all regions, AP & AM & EMEA

"MeasureLevel" table:

column: level0 (Rev_Cost, Rev, Cost)
Rev_Cost includes both Rev and Cost

Users A, B, C, D have roles as below:

User1: PC + Wty + AP + Cost
User2: Monitor + Svc + WW + Rev 

User3:  All_product + Svc + EMEA + Rev_Cost

User4: All_product + All_Wty_Svc + WW + Rev_Cost

How should I set up dynamic RLS base on these different columns from different tables?
I tried to create 4 user list tables (only with 2 columns, user email and business group/business type/region/rev_cost ) and joined with "Product" table, "GEO" table and "MeasureLevel" table separately with many to many (both), then I created 4 different roles like following (this is only one role, but the other 3 are similar)

If I separately tested the role, it worked. But if I choose all these 4 roles together, it won't work.

Do you know how to make these 4 roles work together for 1 user?

Or do you have any better suggestion how to create dynamic RLS for such case? many thanks in advance.

Var vUser = USERPRINCIPALNAME()
VAR vbusiness_group =
CALCULATETABLE(
VALUES('user_list_business_group'[BUSINESS GROUP]), FILTER(ALL('user_list_business_group'), 'user_list_business_group'[user_mail] = vUser)
)
VAR vCheck_All_prod = IF("All_product" in vbusiness_group, TRUE(), [user_mail] = vUser)

RETURN
vCheck_All_prod

[v-stephen-msft · Accepted solution]

Hi @Tracy000 ,

I reviewed the posts and found that no one has responded to the post yet. I'm glad to help you, and from your description, it seems you're attempting to implement a complex RLS strategy that involves multiple attributes across different dimension tables. Your approach of creating user list tables for each attribute and attempting to join them with your dimension tables is logical. However, when multiple roles are assigned to a single user, RLS filters become additive, which might be causing the issue you're experiencing.

Given the complexity of your requirements, here's a suggested approach direction to setup:

1.Given the complexity of your requirements

Instead of maintaining separate user list tables for each attribute (business group, business type, region, rev_cost), consider consolidating these attributes into a single user list table. This table should have columns for user email, business group, business type, region, and rev_cost. This consolidation will simplify your DAX expressions and role definitions.

2.Define Comprehensive DAX Expressions

3.Test Roles Individually and in Combination

4.Review and Optimize

Ensure your model follows best practices for RLS, as discussed in the RLS guidance.

Best Regards,

Stephen Tao

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

[Tracy000 · Accepted solution]

Implemented by divided the user list into 4 separate list, and created 1 dynamic role base on the fiters on these 4 tables.

[v-stephen-msft · Accepted solution]

Hi @Tracy000 ,

I reviewed the posts and found that no one has responded to the post yet. I'm glad to help you, and from your description, it seems you're attempting to implement a complex RLS strategy that involves multiple attributes across different dimension tables. Your approach of creating user list tables for each attribute and attempting to join them with your dimension tables is logical. However, when multiple roles are assigned to a single user, RLS filters become additive, which might be causing the issue you're experiencing.

Given the complexity of your requirements, here's a suggested approach direction to setup:

1.Given the complexity of your requirements

Instead of maintaining separate user list tables for each attribute (business group, business type, region, rev_cost), consider consolidating these attributes into a single user list table. This table should have columns for user email, business group, business type, region, and rev_cost. This consolidation will simplify your DAX expressions and role definitions.

2.Define Comprehensive DAX Expressions

3.Test Roles Individually and in Combination

4.Review and Optimize

Ensure your model follows best practices for RLS, as discussed in the RLS guidance.

Best Regards,

Stephen Tao

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

[Tracy000]

@v-stephen-msft 
Many thanks for your reply and kind suggestion!