IN for multiple values

[Pratyay_Rakshit]

I can now use IN for searching ONE value.

Like

IN (Value, LIST())

Can this be done :

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

?

[Suvrutt_Gurjar]

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?

[Pratyay_Rakshit]

any one item from list A

[Suvrutt_Gurjar · 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.

[Pratyay_Rakshit]

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

[Aleksi]

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

[Pratyay_Rakshit]

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

[Pratyay_Rakshit]

Sorry, my brains stopped decided to be mine.

The conditions would have to be just the reverse.

[Suvrutt_Gurjar]

Pratyay_Rakshit:

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

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

Pratyay_Rakshit:

Sorry, my brains stopped decided to be mine.

The conditions would have to be just the reverse.

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?

[Pratyay_Rakshit]

The error was logical, not syntactic.

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

[Suvrutt_Gurjar]

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?