Multi Layer Row Level Security

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.

@chiu2003

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

1 Like

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.
New%20User
Region

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 :wink:

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?

Capture

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