2 Layers Row Level Security

[Anonymous]

Hi all,

Unfortunately, I got a very ununsal requirement for Access Control

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

How can I achieve 2 layers RLS with user email? I try to use the Organizational Hierarchy concept, but fail.

https://www.dropbox.com/s/7wopvrhs7jgti6g/Hierarchy%20RLS-demo.pbix?dl=0 

Thanks.

[v-frfei-msft]

Hi @Anonymous ,

I have created the role in your sample by the formula.

VAR Email = "Eric@abc.com"
VAR Region =
    CALCULATETABLE (
        VALUES ( 'User Right'[Region] ),
        FILTER ( 'User Right', 'User Right'[Email] = Email )
    )
VAR Country =
    CALCULATETABLE (
        VALUES ( 'User Right'[Country] ),
        FILTER ( 'User Right', 'User Right'[Region] IN Region )
    )
VAR k =
    FILTER ( 'Region/County', 'Region/County'[Region] IN Region )
VAR n =
    CALCULATETABLE (
        VALUES ( 'Region/County'[Country] ),
        FILTER ( 'Region/County', 'Region/County'[Country] IN Country ),
        KEEPFILTERS ( k )
    )
RETURN
    [Country] IN n

You can change the email address to USERNAME function in your side.

BYW, pbix as attached.

[Anonymous]

Thanks for your reply.

Unfortunately, the problem is more complicated as my team would like to simplify the user control table and also apply All in Region.

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.

Do you have any idea how to handle it? Thanks.

https://www.dropbox.com/s/n8oj15b7etvc2sa/Hierarchy%20RLS-demo%20v2.pbix?dl=0 

https://www.dropbox.com/s/8dl3mvkfyiv43qe/ML%20RLS%20data%20v2.xlsx?dl=0 

Many Thanks.