Hi all,
I am working on a simply sales dashboard which has Region (Higher level) and city (Lower level) and I can set up a simply RLS, let say Employee A can only see Region UK result and Employee B only see Region EU result.
But how can i set up multi layer RLS?
For example, Employee A can see all city for Region UK, but Employee C can only see specific city result in Region UK at the same time?
Thanks.
Please look into these resources, I believe you’ll find what you need there.
Unfortunately, it is not a Parent Child or Manager Level Relationship.
Employee C is not report to Employee A, and who can access what country information is quite independent.
It seem I can only use the many to many relationship and I hope no changes on the country name.
I understand this doesn’t apply to your situation one on one but please see if you can translate this methodology.
So try to think of and build a Power Query solution that allows you to identify and apply your RLS rules on so it filters your model if you connect that with your Region table
Hi Melissa,
Unfortunately, my team requirement is strange.
I have upload the demo power bi file for better explanation.
We get Sales table, region/ country mapping table and user access control table.
The key pain point: User access control by Region first, then by Country.
We cannot hard code the country for each user as many countries in each region and country code can change over the time (I only include a few in demo).
“All” mean user can view all country data for that region.
ie: Eric can view all DACH, NL data, but Sam can only view FRANCE in FRANCE/ BELGIUM.
| Name | Region | Country | |
|---|---|---|---|
| Eric | Eric@abc.com | DACH, NL | All |
| Peter | Peter@abc.com | DACH, NL | AUSTRIA |
| Boris | Boris@abc.com | DACH, NL | GERMANY |
| Peter | Peter@abc.com | FRANCE/BELGIUM | All |
| Sam | Sam@abc.com | FRANCE/BELGIUM | FRANCE |
| Henry | Henry@abc.com | UK | All |
| Peter | Peter@abc.com | UK | All |
Is it possible I can have 2 layers RLS? I try to use the Organizational Hierarchy concept, but fail.
Hierarchy RLS-demo.pbix (55.8 KB)
Thanks.
Ok here it goes. Fist I added a concatenation of [Region] and [Country] to your Region table
Next I created a Temp Table containing all valid [Region] and [Country] combinations for each User
Then I expanded the Temp column into [RC Permission] the result added rows for Users
Created a rule for the Region table in your model.
To test it I replaced the USERPRINCIPALNAME() above with a hard coded email address
Here’s the result for: Peter@abc.com
and: Eric@abc.com
If this resolves your issue please don’t forget to mark the thread as solved. Thank you.
ML RLS data.xlsx (12.8 KB) Hierarchy RLS-demo.pbix (72.8 KB)
Hi Melissa,
Thanks. I can mirror it to my demo and It is working perfectly.
Also, I just modify the the Role formula and it seems no issue.
Is it possible to simplify the user control table using “All” in Region instead of specify the Region name? I can create the full Region list from dimension table.
But I would like to understand below M program more, can you please further explain it?
I really appreciate your help in resolving the problem.
Ethan
Hi Ethan,
Sure thing, made a small change and added comments to explane that M code section here:
So here’s the full M statement again:
let
Source = Excel.Workbook(File.Contents(FileLocation), null, true),
#"Filtered Rows" = Table.SelectRows(Source, each ([Name] = "tUser")),
#"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Data"}),
#"Expanded Data" = Table.ExpandTableColumn(#"Removed Other Columns", "Data", {"Name", "Region", "Country", "Email"}, {"Name", "Region", "Country", "Email"}),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Data",{{"Name", type text}, {"Region", type text}, {"Country", type text}, {"Email", type text}}),
BufferRegion = Table.Buffer(#"Region/County"),
Custom1 = Table.AddColumn(#"Changed Type", "Temp", each if [Country]="All" then
let currentLoopupID = [Region] in Table.SelectRows(BufferRegion, each [Region] = currentLoopupID)
else
let currentLoopupID = [Region] &"--"& [Country] in Table.SelectRows(BufferRegion, each [RC Permission] = currentLoopupID)),
#"Expanded Temp" = Table.ExpandTableColumn(Custom1, "Temp", {"RC Permission"}, {"RC Permission"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Temp",{{"RC Permission", type text}})
in
#"Changed Type1"
Changes to the Region table will require you to modify the M statement but now you know what each element does I’m sure that won’t be a problem.
Thanks for your explanation.
But I think I was misinterpret myself in previous post.
Below is my new user control table (All in both Region & Country) and a new table to store all Regions values.
To make it easy, I create a extra step to match for mapping regions first, but I cannot modify the M code successfully (i cannot let All to expand all Regions in my Region table)
If I can expand the Regions and I will use this intermediate table for mapping country as previous demo file.
I will keep study on your M code, but do you think it is workable? Thanks.
ML RLS data v2.xlsx (14.3 KB)
Hierarchy RLS-demo v2.pbix (80.1 KB)
Hi Ethan,
Yeah I didn’t get that, sorry. However a gereral best practice is to always be verry clear and explicit when asking questions on the forum, that way members can provide a solution much quicker 
In my opinion you don’t need a separate Region table, since all combinations are already available in the Region/Country table. The only change that’s required is the logic inside the if statement.
Here’s the full query again.
let
Source = Excel.Workbook(File.Contents(FileLocation), null, true),
tUser5_Table = Source{[Item="tUser5",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(tUser5_Table,{{"Name", type text}, {"Region", type text}, {"Country", type text}, {"Email", type text}}),
BufferRegion = #"Region/County",
#"Added Custom" = Table.AddColumn(#"Changed Type", "Temp", each if [Region]="All" then BufferRegion[[RC Permission]] else if [Country]="All" then let currentLoopupID = [Region] in Table.SelectRows(BufferRegion, each [Region] = currentLoopupID) else let currentLoopupID = [Region] &"--"& [Country] in Table.SelectRows(BufferRegion, each [RC Permission] = currentLoopupID)),
#"Expanded Temp" = Table.ExpandTableColumn(#"Added Custom", "Temp", {"RC Permission"}, {"RC Permission"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Expanded Temp",{{"RC Permission", type text}})
in
#"Changed Type1"
And this is a preview of the result.
Bonus tip.
There’s a Parameter called “FileLocation” if you change the file path there (via Mangage Parameter) all your queries will be restored and you don’t have to change them one by one inside the Advanced Editor.
If this resolves your issue, please mark this post as solved - thank you
Hierarchy RLS-demo v2.pbix (82.9 KB)
Kind regards,
Melissa
Thanks. I believe it should work. I will try it when I can use my laptop.
A very quick question, how can I add the BufferRegion in Power Query? I must do that with Advanced Editor?
Great thanks!
Not necessarily, you could just as easily insert this:
= #“Region/County”
in the formula bar (by pressing the fx in front of it) and rename it in the Applied Steps section but the most important thing to remember is that in the next step you don’t reference BufferRegion but the step before that so #“Changed Type”.
Since Power Query is verry polite it’ll always reference the previous step automaticly but in this case you do not want that because you need to ‘return’ to the actual table you are working on in this case User Right (Step 1 Match Region) and to do that you need to reference #“Changed Type”.
Fantastic solution Melissa. This is really great work, well done.
Thanks Melissa,
You solution is work for me.
I just getting some new ideas, can I also apply the same logic to limit the sales person view for user?
For example, Boris can only view Germany and SP1 sales, but Peter can view all sales person record for all Region
ML RLS data v2.xlsx (16.2 KB) Hierarchy RLS-demo v2 (1).pbix (101.2 KB)
Thanks.
Hi ,
Is it possible you can have a tutorial series on M code?
I will open a new post for very similar issue
Hi @Melissa
Was going through this solution today. Absolutely loved it. You have excellent knowlede on Power Query 
New learning around BufferRegion[[RC Permission]], I was aware that we can create list like BufferRegion[RC Permission], didn’t know we can also create table by using double brackets.
Thanks
Ankit Jain
@ankit Glad to hear you’ve found it helpful.
I enjoyed working on it.
Hi @Melissa
Your solution solves my problem i posted recently Row Level Security User Profile Issue - Power BI - Enterprise DNA Forum
Thank you for saving my day 
But when i try to implement this solution , i can no longer load my dashboard.
Could you please help me on this topic?
Thank you
Hi @Tahar,
Please do not post to solved threads. Rather, start a new thread and link to a solved thread if it pertains to your issue. This will ensure visibility for your issue and is also for your benefit, as many forum members do not view solved threads.
Thanks
Keith
