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

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

Reply
MrKETATA
Frequent Visitor

multi RLS > subtractive rather than additive

hello community,

 

USECASE 1
Suppose we have a classic star schema, with dim product and dim region.
requirement = we need to implement an RLS rule, users under this rule should only see "Bikes" category sold in "Europe" region 
which means an intersection-based RLS between dim product and dim region 
The common approach is to create 2 seperate static RLS with one,  filtering "dim product" and the other filtering "dim region"

but in that case, those 2 RLS with be additive and we'll see , say some Bikes data saled on "US" region for example.
    

USECASE 2 :
i've created 2 RLS
RLS 1 = filtering a standalone table not related to the model 
RLS 2 = filtering a dimension table related to the model (star schema)
both tables blong to the same dataset (no way to seperate them due to a specific requirement)

as a result of RLS additive behavior, and from the fact that those 2 tables are not related, 
applying both RLS at the same time, made the data fully accessible.

any workarounds except createing seperate parameterized datasets ?

Thank you!

2 REPLIES 2
v-chenwuz-msft
Community Support
Community Support

Hi @MrKETATA ,

 

Your usecase 2 is the usual method.

1 add a role table( seperate parameterized dataset)

2 filter product and region table by two rls rules.

rls code like this:

 

product table:

[product_id] in calculatetable( values(roletable[productid]), filter( roletable, [useremail] = username()))

 

region table:

[region_id] in calculatetable( values( roletable[region_id]), filter( roletable,[useremail] = username())) 

 

Best Regards

Community Support Team _ chenwu zhu

 

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

Thanks @v-chenwuz-msft 


The thing is, table in RLS1 will be used to assign our end userprincipalname() to a specific page name (has no business with data model), although, RLS2 is meant to filter our data model, finally i'll go with multi parameterized dataset, and leverage RLS1 only, I would suggest to disable RLS additive behavior on tables that are obviously not related.

Regards.

Helpful resources

Announcements
March PBI video - carousel

Power BI Monthly Update - March 2025

Check out the March 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

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

Top Solution Authors
Top Kudoed Authors