Need Help with DAX for Dynamic RLS to avoid bi-directional relationship

[Anonymous]

Hi all, 

I have to implement dynamic RLS on country where one user is allowed to see their own country data wheen user is not manager and if user is manager(IsManager=1)they should see all countries data means no RLS.

Please note that I shouldn't use bidrectional relationship in my data model so that role should be applied on country table that should filter FACT table.

Employee-Country table looks like below

| UserID | Email Address | Country    | IsManager |
|--------|---------------|------------|-----------|
| 1      | a.a@gmail.com | Finland    | 0         |
| 2      | r.e@gmail.com | Italy      | 0         |
| 3      | l.c@gmail.com | Spain      | 0         |
| 4      | p.l@gmail.com | Belgium    | 0         |
| 5      | r.f@gmail.com | AllCountry | 1         |
| 6      | m.k@gmail.com | AllCountry | 1         |
| 7      | m.c@gmail.com | Finland    | 0         |
| 8      | u.p@gmail.com | Belgium    | 0         |
| 9      | p.j@gmail.com | Finland    | 0          |

Country Table

| Country | CountryID |
|---------|-----------|
| Finland | 1         |
| Belgium | 2         |
| Italy   | 3         |
| Spain   | 4         |

Fact Table

| FactID | Country | Amount |
|--------|---------|--------|
| a      | Finland | 100    |
| b      | Italy   | 200    |
| c      | Spain   | 300    |
| d      | Belgium | 400    |
| e      | Belgium | 200    |
| f      | Italy   | 150    |
| g      | Spain   | 300    |
| h      | Spain   | 390    |
| i      | Belgium | 400    |

Data Model looks like

Employee Country and Country is Joined on Country column

Fact table is joined with Country on Country column

I am trying the below DAX in Country table but its not working when user is not manager 

IF(
LOOKUPVALUE('Employee-Country'[IsManager],'Employee-Country'[Email Address],USERPRINCIPALNAME())=1,
TRUE(),
MAX('Employee-Country'[Email Address])= USERPRINCIPALNAME())

Any help would be much appreciated

Thank you in advance!

[v-jayw-msft]

Hi @Anonymous ,

Will you consider to change the direction of the relationship to "Both" and check again?

Best Regards,

Jay

[Anonymous]

Thanks @v-jayw-msft for your response, It works fine with bidirectional relationship

[Anonymous]

@Jihwan_Kim  could you please help with DAX whenever you will have time . 
Thank you