Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

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
EricEric@abc.comDACH, NLAll
PeterPeter@abc.comDACH, NLAUSTRIA
BorisBoris@abc.comDACH, NLGERMANY
PeterPeter@abc.comFRANCE/BELGIUMAll
SamSam@abc.comFRANCE/BELGIUMFRANCE
HenryHenry@abc.comUKAll
PeterPeter@abc.comUKAll

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.

 

 

2 REPLIES 2
v-frfei-msft
Community Support
Community Support

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

Capture.PNG

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

BYW, pbix as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Anonymous
Not applicable

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.

New User.PNGRegion.PNG

 

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)

Step1.PNGIncorrect Map.PNG

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.