Lookup multiple values to filter table for RLS

[Anonymous]

I can't find an answer and i've tried several things but I need to filter a table for my RLS. I need to filter my table with a list of Inspectors based on the unit they are in. I have many tables but because of the relationships  i couldnt do a direct relationship between the Unit and the employee table. And i cant change directionality.

So im thinking my solution is to grab the values from my filtered Unit table and filter Inspector table based on that.
Here's a snippet of relationships. i underlined the field they are connected with by color

I have an RLS filter on my Unit table to determine what  units to show based on what Regional VP is logged in:

[RVP_Email] = userprincipalname()

and that works fine. and it Filters the Unit list, so i need to take the unit_numbers that are left after being filter and then filter the inspector table so i can have the list of inspectors.

Ive tried things similar to:

• LOOKUPVALUE but i know that can only return a single value

• CALCULATE ( VALUES(INSPECTION[unit_number]),
  FILTER (
  ALLSELECTED( INSPECTION ),
  INSPECTION[unit_number] in VALUES(INSPECTOR_UNIT[unit_number] ) )
  )

•  [unit_number] in VALUES(INSPECTOR_UNIT[unit_n

But i get the error in measures, on the RLS box.  

Any help would be greatly appreciated, Thanks!

[AllisonKennedy · Accepted solution]

The unit table doesn't filter the inspector table - cross filter direction won't let the unit flow against the arrow into inspector table. (I don't recommend both cross filter direction, but that would allow the filtering). 

Also be sure to tick 'apply security filters in both directions' when you have both cross filter direction. 

Not sure if this trick also works with RLS, but you could try creating a filter to show inspectors only when COUNTROWS(Inspection)>0

https://www.sqlbi.com/articles/bidirectional-relationships-and-ambiguity-in-dax/