I can now use IN for searching ONE value.
Like
IN (Value, LIST())
Can this be done :
IN({Value1, Value2, Value3…}, LIST())
?
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.
3 Likes
Or if you need to check that they all belong to the list…
COUNT(INTERSECT({A,B},{LIST}))=COUNT({A,B})
2 Likes
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
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.
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
-
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.
-
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?
1 Like
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?
2 Likes