Power BI RLS with multiple values

[WilliamA]

Hi all,

I am struggling with implementing RLS  across multiple fields/tables when there are multiple records for the same user. 

My security table looks like the following

UserID  Division   Area   Sub  Module Report

User1	Australia	NSW	All	A	1001
User1	Australia	QLD	All	A	1001
User1	Australia	QLD	All	A	1002
User2	All	All	All	B	All

The field names are all seperate tables in my report and my role statement is as below - 

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

the above is applied to all required tables. This works fine when there is only one record for each user but as you can see from the table above, a user can belong to different groups with different access.

How can I implement RLS based on the above table?

[parry2k · Accepted solution]

@WilliamA I think you need to change 2nd condition:

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