IN for multiple values

I can now use IN for searching ONE value.

Like

IN (Value, LIST())

Can this be done :

IN({Value1, Value2, Value3…}, LIST())

?

Solved Solved
0 10 606
1 ACCEPTED SOLUTION

Please try something like below

COUNT(INTERSECT(LISTA , LIST B)) >0

Or following

COUNT(INTERSECT({A, B, C}, {A, B, C, D, E, F}))>0
in a constraint or Y/N type column or filter or condition type of setting

Or something like below if your using the statement in say text type column

IF( COUNT(INTERSECT({A, B, C}, {A, B, C, D, E, F}))>0 , “Whatever message you wish on statement being true”, “Whatever message you wish on statement being false”)

Edit: Minor correction in typo errors.

View solution in original post

10 REPLIES 10

Could you update what do you mean by “search”? IN() function confirms in terms of TRUE or FALSE whether an item is present in a list?

So are you trying to know if all the items in the list are in another list and for a complete TRUE or partial TRUE? Meaning if you are searching LIST A with 3 items in list B with 6 items and if all 3 items from the list A are present in list B you wish to return TRUE or if any one to 3 items of LISt A are returned in LIST B , you wish to return TRUE?

any one item from list A

Please try something like below

COUNT(INTERSECT(LISTA , LIST B)) >0

Or following

COUNT(INTERSECT({A, B, C}, {A, B, C, D, E, F}))>0
in a constraint or Y/N type column or filter or condition type of setting

Or something like below if your using the statement in say text type column

IF( COUNT(INTERSECT({A, B, C}, {A, B, C, D, E, F}))>0 , “Whatever message you wish on statement being true”, “Whatever message you wish on statement being false”)

Edit: Minor correction in typo errors.

What would be the reverse condition to specify?

COUNT(INTERSECT({A, B, C}, {A, B, C, D, E, F}))<0 or
COUNT(INTERSECT({A, B, C}, {A, B, C, D, E, F}))=0

Or if you need to check that they all belong to the list…
COUNT(INTERSECT({A,B},{LIST}))=COUNT({A,B})

ifs(
and([Current Stage]=“Lost”, COUNT(INTERSECT({"Sold”, “Door Ordered”, “Job Scheduled”, “Job Completed”, “Payment Received”, “Feedback Requested”}, select(Leads[Stage], [Customer]=[_THISROW]))>0), “Inactive Prospect”,

and([Current Stage]=“Lost”, COUNT(INTERSECT({"Sold”, “Door Ordered”, “Job Scheduled”, “Job Completed”, “Payment Received”, “Feedback Requested”}, select(Leads[Stage], [Customer]=[_THISROW]))=0), “Inactive Customer”,

and([Current Stage]<>“Lost”, COUNT(INTERSECT({"Sold”, “Door Ordered”, “Job Scheduled”, “Job Completed”, “Payment Received”, “Feedback Requested”}, select(Leads[Stage], [Customer]=[_THISROW]))>0), “Active Prospect”,

and([Current Stage]<>“Lost”, COUNT(INTERSECT({"Sold”, “Door Ordered”, “Job Scheduled”, “Job Completed”, “Payment Received”, “Feedback Requested”}, select(Leads[Stage], [Customer]=[_THISROW]))=0), “Active Customer”
)

This is the expression I entered. It doesn’t work in the way I want it to do

Sorry, my brains stopped decided to be mine.

The conditions would have to be just the reverse.

Could you elaborate the above, in what way it does not work??

Good to know you found the reason

Syntactically I believe you may wish to check 2 possible errors in the expression

  1. If the table in which this expression is there has [Customer] as key, then even [Customer]=[_THISROW] is OK as you have written or if the key is different then you may wish to try [Customer]=[_THISROW].[Customer] instead.

  2. I believe you have one parenthesis less ( 3 instead of 4 ) in each of the sub expressions in closing side. I have highlighted the closing parenthesis in yellow that is missing in each of the 4 subexpressions.

Should it be as follows?

The error was logical, not syntactic.

The four conditions, where there are two > and two = after the intersect expression, they needed to interchange places.

Great. Thank you.

May I request that you share the final working expression, if possible, for the benefit of future readers of this post thread as an example of using equivalent of IN() with lists?

Top Labels in this Space