On the face it seems simple but, I’m struggling to find the expression that is both valid and acceptable as a security filter. (Virtual Columns are not an option with security fields)
To summarize; Two separate tables, People and Apps. Each table has a single field [Crew]
In the People table, a person can belong to more than one crew. This is saved in field People[Crew]. If this field contains multiple crews, (person belongs to multiple crews) each value is separated with a coma and a space: Crew1, Crew2.
In the Apps table, the [Crew] field determines which crew(s) can access the app specified but the row. Each app can be accessed by several multiple crews:
App ‘A’ can be accessed by Crew1, Crew2
App ‘B’ can be accessed by Crew1, Crew3 (but not crew 2)
The following expression works fine so long as the person only belongs to ONE crew. If additional crews are added to People[Crew], the expression returns no rows from the Apps table.
From the Apps table I set the following as the security filter:
CONTAINS([Crew], SELECT(People[Crew], [UserEmail]=USEREMAIL()))
I have tried many different combinations of IF(), INTERSECT(), CONTAINS(), etc and all fail for one reason or another. The expression is either outright invalid, valid or cannot be used as a security filter (VCs), or is valid but doesn’t return any values.
I’d appreciate anyone that could get me pointed in the right direction…