Row Filter for referenced table

[SKETCHwade]

Hi All,

I am trying to create a slice based on what a reference table contains

The expressions I have tried will explain it all

This Filter expression is telling me I am not using the correct Filter parameters:

FILTER([Related Tasks],AND([Allocated To]="Honing",[Completed?]=False))

This "Works" but returns rows that contain "Honing" even if [completed?] = TRUE:

AND(
CONTAINS([Related Tasks][Allocated To],"Honing"),
CONTAINS([Related Tasks][Completed?],"FALSE")
)

Any Ideas?

[TeeSee1 · Accepted solution]

Try

AND(
 IN("Honing", [Related Tasks][Allocated To]),
 NOT(
  IN(TRUE, [Related Tasks][Completed])
 )
)

[SKETCHwade]

Thank you @TeeSee1  This worked perfectly!

[Suvrutt_Gurjar]

I believe your current sub expressions work individually at their own group level, meaning condition "Honing" is checked independently for the related records and then condition FALSE is checked independently for those related records before being combined.

If you wish to filter at individual child table record level for those conditions, you could add a VC called say [SliceCheck]  in the child table with an expression something like 

IF( AND([Allocated To]="Honing", NOT([Completed?])), " Include", "Exclude")

Then your slice expression could be 

CONTAINS([Related Tasks][SliceCheck],"Include")

[Suvrutt_Gurjar]

Just thought of sharing the salient points of both the approaches.

The one suggested with creating a VC in child table is based on the principle that, do the local processing of child table columns in the child table itself. Then have a simpler expression outside the table , slice filter in this case. 

CONTAINS([Related Tasks][SliceCheck],"Include")

However, the configuration procedure is a bit longer as it requires creation of a VC. However, if the conditions in the child table increase, the approach may be better as the the conditional processing is local or else multiple lists may need to be processed outside the table.

The other approach takes the  all the processing outside the table, so it queries two lists outside the child table, in this case the slice expression. However the configuration is bit easier as it does not require the VC creation.