Dynamic RLS based on two dimension table's

[unnijoy]

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	Name	Country	Product
Alex@ybl.com	Alex	US	Cable
Rex@ybl.com	Rex	China	ALL
Glen@ybl.com	Glen	ALL	ALL

 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.

[unnijoy]

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..

[Jeanxyz]

@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 · Accepted solution]

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]

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

[Jeanxyz]

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.