After doing all this below I found that all that may be required is to use. Try if not read the rest as it also works.
The problem is that the Employees[Adminstrator] contains either YES or NO so you might have
So the issue is that there will be multiple YES’s and multiple NO’s so how does the IN() function determine when there are repeated values. So the IN function only determines whether a single YES exists and returns YES but not Y/N for each row as shown below.
What you are looking for is to find every value in Employees[Administrator] which has “YES” as they are administrators. This may be done with INTERSECT(LIST(“YES”), Employees[Administrator]) but which is a LIST and not a boolean. So now you need to produce a boolean from this list.
If you use IN (LIST(“YES”),LIST([Flagged])) you get
Which is what you require.
You already know that INTERSECT will return a list all YES values so now you need a function which confirms this and converts this to boolean
So the INTERSECT filters all values which have YES and the CONTAIN confirms that each and every list value returned is indeed “YES” and you get a single boolean return value. So it confirms that each and every value in the array / list is “YES”.
Adding the CONTAIN is a redundant step but it serves to return a BOOLEAN from a LIST of identical values.
CONTAIN() is case sensitive so YES <>= Yes for example.
I quickly tested this and this is what I got
This is just to illustrate why seemingly simple expressions and logic do not function as one would expect.
It appears to work but note that in the CONTAIN I had to make each variable of type LIST.
Thus the above would then become
Note that APPSHEET requires incremental debugging of expressions as return values may change so one has to save an incorrect expression to reflect the correct type and then it works.
Which is correct as it only identifies rows in column Flagged that are YES and all other are blank.
But it is of type LIST and you require BOOLEAN
Now if you add CONTAIN it gives error as it requires type YES/NO
Now save and change to Type Y/N and you get
and the result is
Which is what you wanted and which return a BOOLEAN for each row with YES.
As Appsheet returns different types one has to take care of how one develops the expression.
Now if I add a SELECT it returns just the rows with YES
SELECT (park[Primary Key],CONTAINS(INTERSECT(LIST(“YES”),LIST([Flagged])),“YES”))
You get the same result using SELECT(park[Primary Key],IN (LIST(“YES”),LIST([Flagged])))
Note that using Employees[Administrator] and [Administrator] gives different results when using IN()