Security expression for main table and order table

[Phil_Waite]

Hi,

I have an expression in a scurity filter for a main table. There is an orders table.

I want to show rows in the main table that have an order in the orders table that does not have an [Order Status] containing "Archived".

[Outlet No] is the unique Id in the main table and it appears as a column in the orders table

So far I have

AND(IN([Outlet No],Orders[Outlet No]),

NOT(CONTAINS(ANY(SELECT(Orders[Order Status], ([Outlet No] = [_THISROW].[Outlet No]),"Archived")))

[Steve]

If the Order ID column of the Main table is of type Ref to the Orders table, all you need is this as your security filter expression:

("Archived" <> [Order ID].[Order Status])