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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
unnijoy
Post Partisan
Post Partisan

Dynamic RLS based on two dimension table's

Hi I have a fact table which got Country ,Product and total sales. I create two dimenion tables one for country and other for Product.

I need to create a Dynamic RLS. Fot this purpose i create a access list based on email ID, Country and Product.

In the Access list table i will enter the email id of manager's. and whcih country and product they need access to. Thier will be some manager's who nead access to all countrie's and Product. for them under Country and Product i will add "ALL".

Below is the dax that i use in RLS. it wil working for all except for the people for whom i add "ALL".

For Country -var _Restriction =
CALCULATETABLE(
VALUES( Access_List[Country] ),
Access_List[Email ] = USERPRINCIPALNAME()
)
var _RLS_Rest =
SWITCH(
TRUE(),
_Restriction = "ALL", TRUE(),
[Country] = _Restriction, TRUE(),
FALSE()
)
Return
_RLS_Rest

 

For Product-var _Restriction =
Calculate(
CALCULATETABLE(
VALUES( Access_List[Product] ),
Access_List[Email ] = USERPRINCIPALNAME()
),
USERELATIONSHIP( Access_List[Product], Product_Access[Product]))
var _RLS_Rest =
SWITCH(
TRUE(),
_Restriction = "ALL", TRUE(),
[SET] = _Restriction, TRUE(),
FALSE()
)
Return
_RLS_Rest

 

Below is ma Access list table.

Email NameCountryProduct
Alex@ybl.comAlexUSCable
Rex@ybl.comRexChinaALL
Glen@ybl.comGlenALLALL

 As per the above table RLS is working fine for Alex.  But for Rex and Glen every thing is coming as blank.

 

What may be the reason.

 

 

1 ACCEPTED SOLUTION
Jeanxyz
Post Prodigy
Post Prodigy

I assumed in your access list, you have list each combination of countries and products per user. If that's not the case, here is what I will do

1. for users who have access to all countries and products, add them as members of the workspace. This will give them access to the whole report, RLS has no impact on them. 

2. for users who have access to all products in a single country, create a dim_single county table and link this table to fact table, the key is Product column

3. for users who have access to single/a few products in a single countries, create dim_country_product table and link it to fact table based on product and country. 

 

However, I admit this approach is a bit cumbersome, maybe other people have a better solution. 

 

 

@unnijoy , @amitchandak 

View solution in original post

5 REPLIES 5
unnijoy
Post Partisan
Post Partisan

I don't understand.. who ever accpet this as a solution didn;t read the issue that i am facing. Giving access to the workspace is easy. if that was the case then we don;t need RLS... can really some help me with this. And some ple tell me is it possible for somelese can click on accept solution otherthan me... Please help..

@unnijoy The forum admin (usually MS employees) has the right to accept the solution as answers on your behalf. You should receive a request from MS if you have received a solution. If no response from your side, MS admin will tick an answer as solution on your behalf. 

 

RLS works, but workspace members are like super users, they can have access to the full report even if you limit their access to a certain country in your RLS table. 

Jeanxyz
Post Prodigy
Post Prodigy

I assumed in your access list, you have list each combination of countries and products per user. If that's not the case, here is what I will do

1. for users who have access to all countries and products, add them as members of the workspace. This will give them access to the whole report, RLS has no impact on them. 

2. for users who have access to all products in a single country, create a dim_single county table and link this table to fact table, the key is Product column

3. for users who have access to single/a few products in a single countries, create dim_country_product table and link it to fact table based on product and country. 

 

However, I admit this approach is a bit cumbersome, maybe other people have a better solution. 

 

 

@unnijoy , @amitchandak 

unnijoy
Post Partisan
Post Partisan

@Jeanxyz , how will that help me to resolve this "ALL" issue?. Sorry am not getting it.

Jeanxyz
Post Prodigy
Post Prodigy

What if you create a key column in both fact table and Access-List? The key column should be Country_Product, you can then link the two tables via the key column. 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors