Dynamic RLS with Hierarchy

[WilliamA]

Hi all,

I have this requirement to implement RLS against multiple columns. I have 4 single level hierarchy tables called Country, State, Area, Module. These tables do not relate to each other.

My Security table looks like the below 

UserID                    Country                     State                   Area             Module

User1	Australia	NSW	14	1001
User1	Australia	NSW	14	1002
User1	Australia	NSW	14	1003
User1	New Zealand	All	12	1004
User2	Australia	All	All	All

As an example with my current RLS setup, if User 1 logs in then they have access to Australia and New Zealand, All States, Area 14 and modules 1001 -1003. However, I need to apply security on a row by row level.  for example, if User1 logs in they they should see Australia,NSW,14,1001 or Australia,NSW,14,1004, or New Zealand,All,12,1004

My current DAX query is below - As you can see it doesn't take into account all columns and treats it column by column. the same DAX is applied to all my hierarchy tables.

How can I implement RLS that returns true if a record matches Country, State, Area and Module?

Country Hierarchy Table:

var _Restriction =
CALCULATETABLE(
VALUES('Test Security'[Country]),
'Test Security'[UserID] =USERPRINCIPALNAME()
)
Return
SWITCH(
TRUE(),
"All" in _Restriction, TRUE(),
[Country] in _Restriction , TRUE(),
FALSE()
)

[amitchandak]

@WilliamA , I have done a workaround to handle all here, check if that can help to create your solution

Power BI- Row Level Security(RLS): Handle ALL, UserPrincipalName: https://youtu.be/KVLEnIUo4pc

[WilliamA]

HI @amitchandak,

I don't think the video quite solves it. your video works on the basis of one table and one column. 

in my scenario,  it needs to filter the data on multiple conditions. 

ie, it shouldn't apply rls on one column at the time. I need to do something like 

User1 has access to this country from table 1 and this state from table 2 and this area from table 3 in one line not individual. 

If you take the below example it may it clear it.

UserID: User 1, Country = Australia, State = NSW

UserID: User 1, Country = New Zealand, State = All

Using the current method, User 1 can see all States for Australia and New zealand where in fact user 1 should only see NSW for Australia and all of New Zealand.