Forum Discussion
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 a...
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.
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)