Forum Discussion
SEARCH Function with IF Statement
I am trying to flag a list of item in a Product Name column using the SEARCH function. I have tried using both an IF Statement as well as a SWITCH function. Essentially I am wanting to flag in a calculated column if a product meets the criteria and then I will use that 1,0 flag in future calculations. Can someone help me with the following formulas?
=If(
SEARCH("*Gift Card*", 'Product'[ProductName])
|| SEARCH("*Play*", 'Product'[ProductName])
|| SEARCH("*Rental*", 'Product'[ProductName])
|| SEARCH("*Refund*",'Product'[ProductName])
|| SEARCH("*Service Fee*", 'Product'[ProductName])
|| SEARCH("*Prior Day*", 'Product'[ProductName])
|| SEARCH("*Activation*", 'Product'[ProductName])
|| SEARCH("*Award*", 'Product'[ProductName])
|| SEARCH("*Booking*", 'Product'[ProductName])
|| SEARCH("*Ticektless*", 'Product'[ProductName])
|| SEARCH("*Lanyard*", 'Product'[ProductName]), 1,0)
This is the switch funtion I tried using:
= SWITCH(
True(),
SEARCH("*Gift Card*", 'Product'[ProductName],,1)
,SEARCH("*Play*", 'Product'[ProductName],,1)
,SEARCH("*Rental*",'Product'[ProductName],,1)
,SEARCH("*Refund*", 'Product'[ProductName],,1)
,SEARCH("*Service Fee*", 'Product'[ProductName],,1)
,SEARCH("*Prior Day*", 'Product'[ProductName],,1)
,SEARCH("*Activation*", 'Product'[ProductName],,1)
,SEARCH("*Award*",'Product'[ProductName],,1)
,SEARCH("*Booking*",'Product'[ProductName],,1)
,SEARCH("*Ticketless*", 'Product'[ProductName],,1), BLANK())
Anonymous try this
=If( SEARCH("*Gift Card*", 'Product'[ProductName],,-1)>=0 || SEARCH("*Play*", 'Product'[ProductName],,-1)>=0 || SEARCH("*Rental*", 'Product'[ProductName],,-1)>=0 || SEARCH("*Refund*",'Product'[ProductName],,-1)>=0 || SEARCH("*Service Fee*", 'Product'[ProductName],,-1)>=0 || SEARCH("*Prior Day*", 'Product'[ProductName],,-1)>=0 || SEARCH("*Activation*", 'Product'[ProductName],,-1)>=0 || SEARCH("*Award*", 'Product'[ProductName],,-1)>=0 || SEARCH("*Booking*", 'Product'[ProductName],,-1)>=0 || SEARCH("*Ticektless*", 'Product'[ProductName],,-1)>=0 || SEARCH("*Lanyard*", 'Product'[ProductName],,-1)>=0 , 1,0)
2 Replies
Anonymous try this
=If( SEARCH("*Gift Card*", 'Product'[ProductName],,-1)>=0 || SEARCH("*Play*", 'Product'[ProductName],,-1)>=0 || SEARCH("*Rental*", 'Product'[ProductName],,-1)>=0 || SEARCH("*Refund*",'Product'[ProductName],,-1)>=0 || SEARCH("*Service Fee*", 'Product'[ProductName],,-1)>=0 || SEARCH("*Prior Day*", 'Product'[ProductName],,-1)>=0 || SEARCH("*Activation*", 'Product'[ProductName],,-1)>=0 || SEARCH("*Award*", 'Product'[ProductName],,-1)>=0 || SEARCH("*Booking*", 'Product'[ProductName],,-1)>=0 || SEARCH("*Ticektless*", 'Product'[ProductName],,-1)>=0 || SEARCH("*Lanyard*", 'Product'[ProductName],,-1)>=0 , 1,0)