Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

2 Layers Row Level Security

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.


NameEmailRegionCountry
Eric[email protected]DACH, NLAll
Peter[email protected]DACH, NLAUSTRIA
Boris[email protected]DACH, NLGERMANY
Peter[email protected]FRANCE/BELGIUMAll
Sam[email protected]FRANCE/BELGIUMFRANCE
Henry[email protected]UKAll
Peter[email protected]UKAll

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.

 

 

Data Protection
Need Help

2 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    6 years ago

    Hi Anonymous ,

     

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

    VAR Email = "[email protected]"
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's avatar
      Anonymous
      Not applicable
      6 years ago

      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.