Dynamic RLS with 2 columns

[Anonymous]

Hi all,

I am trying to create a dynamic RLS.

Cretiria: 

RLS - code = "All", can view all data

RLS - code = "HK_store1" can only view HK_store1's data

RLS - code = "HK" can view all stores' data from HK (store1 & store2.....)

RLS:

Name	Email Address	code
userA	A@test.com	All
userB	B@test.com	HK_store1
userC	C@test.com	HK_store2
userD	D@test.com	TW_store1
userE	E@test.com	HK

RLS - [code] --> Fact Table - [Store Code]: Security Filter in both direction

Fact Table:

Store Code	Mkt Code
TW_store1	TW
HK_store1	HK
HK_store2	HK
All	All
HK	HK

I am currently using this code for RLS control, could you please advise how I could write the logic for

if RLS - code = "HK" then Fact Table - Mkt code ="HK

VAR _Role = MAXX(FILTER( RLS, RLS[Email Address] = USERNAME() ), RLS[code])
RETURN
if (_Role = "All", TRUE(),
[Email Address] = USERNAME() )

Great thanks if any suggestions.

[amitchandak · Accepted solution]

@Anonymous , One of the option such case to have table like

Store	All Store
HK	HK
HK_store2	HK_store2
TW_store1	TW_store1
HK_store2	All
TW_store1	All
HK	All

Merge this with RLS table using code and all store , then you will get column with store that you can join with fact and filter on it