Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
78 | |
75 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |