How to get around the 3 argument limit

[Earl40]

Im trying to do  a custom column in PowerBi with if statement with a contains string to search for text  that contains a specific word. theres 4 terms i need to search for. Im getting an error that maximum argument is 3. Is there anyway to get around this limit?

Thank you in advance

[Greg_Deckler · Accepted solution]

@Earl40 Try:

Column =
  SWITCH(TRUE(), 
    SEARCH("apple", [Column], 1, 0)>0, "apple",
    SEARCH("peach", [Column], 1, 0)>0, "peach",
    SEARCH("pickle", [Column], 1, 0)>0, "pickle",
    SEARCH("banana", [Column], 1, 0)>0, "banana",
    "not found"
  )

[Greg_Deckler]

@Earl40 In addition to @audreygerred suggestion, you can also use the SWITCH(TRUE(), ...) function to have multiple different logical tests and return values. Much better than nested IF statements which would be another route to go, IF( <logical test>, <result if true>, IF( <another logical test>, <result if true>, ...)

[audreygerred]

Hello! You can try the below:

Found in Field =
VAR CurrentReason = Table[Field]
RETURN
IF (
NOT ISBLANK(CurrentReason) &&
(
SEARCH("SpecificWord1", CurrentReason, 1, 0) > 0 ||
SEARCH("SpecificWord2", CurrentReason, 1, 0) > 0 ||
SEARCH("SpecificWord3", CurrentReason, 1, 0) > 0 ||
SEARCH("SpecificWord4", CurrentReason, 1, 0) > 0
),
"Match",
"No Match"
)

[Earl40]

thanks. but i want the column to populate the column with that word if its foiund example if apple is found then apple, if pear then pear etc

[Greg_Deckler]

@Earl40 Try:

Column =
  SWITCH(TRUE(), 
    SEARCH("apple", [Column], 1, 0), "apple",
    SEARCH("peach", [Column], 1, 0), "peach",
    SEARCH("pickle", [Column], 1, 0), "pickle",
    SEARCH("banana", [Column], 1, 0), "banana",
    "not found"
  )

[Earl40]

@Greg_Deckler i got an error that said switch doesnt support comparing values t/f  with value type integer

[Greg_Deckler · Accepted solution]

@Earl40 Try:

Column =
  SWITCH(TRUE(), 
    SEARCH("apple", [Column], 1, 0)>0, "apple",
    SEARCH("peach", [Column], 1, 0)>0, "peach",
    SEARCH("pickle", [Column], 1, 0)>0, "pickle",
    SEARCH("banana", [Column], 1, 0)>0, "banana",
    "not found"
  )

[Earl40]

@Greg_Deckler  thanks greg it worked. Only issue im having is one of terms has 3 letters that are causing a false positive. Example one of terms i want to search for is CAB  if the sentence contains CABBAGE its picking it up as CAB. is ther anyway to adjust the code to only write CAB if the word is CAB and not part of a longer word?

not sure if it makes it easier but the partv of the paragraph where it would be is always after a = 

[Greg_Deckler · Accepted solution]

@Earl40 Search for "CAB " with the space at the end? Might have an issue with if it is at the end maybe?

[AlexisOlson]

You can make it more dynamic if the list of keywords is lengthy or changing frequently.

VAR _KeyWords_ = { "apple", "peach", "pickle", "banana" }
VAR _AllMatches_ = FILTER ( _KeyWords_, CONTAINSSTRING ( [TextCol], [Value] ) )
VAR _SingleMatch = MAXX ( _AllMatches_, [Value] )
RETURN
    _SingleMatch