Condensing multiple If SEARCH dax formula

[JDro]

Hi all,

Trying to condense down a formula where I need to search for multiple values in a column then output one of two outcomes, its working fine with many nested if(search( functions however I wanted to see if I could use double pipe operators to get it a bit tidier, is there a simple way to do so, the below code is giving me the error "cannot convert type text to true/false":

Formula =

IF(SEARCH("a"||"b"||"c"||"d"||"e",Table[col1],,0)>0,

"outcome1",

IF(SEARCH("f"||"g",Table[col1],,0)>0,

"outcome2",""))

thanks for any advice you can offer.

[Fowmy · Accepted solution]

@JDro 

You can try this measure:

Formula = 
SWITCH(
    TRUE(),
    NOT ISEMPTY(INTERSECT({"a","b","c","d","e"},VALUES('Table'[Column1]))), "Outcome1",
    NOT ISEMPTY(INTERSECT({"f","g"},VALUES('Table'[Column1]))), "Outcome2",
    "No Outcome"
) 

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

[JDro]

That works great thanks.

[Fowmy]

@JDro 

Great, you can accept it as a solution as well. 

________________________

If my answer was helpful, please click Accept it as the solution to help other members find it useful

Click on the Thumbs-Up icon if you like this reply 🙂

Website YouTube  LinkedIn

[AlB]

There'@JDro

Formula = 
SWITCH (
    TRUE (),
    COUNTROWS (
        FILTER ( { "a", "b", "c", "d", "e" }, SEARCH ( [Value], Table1[col1],, 0 ) > 0 )
    ) > 0, "outcome1",
    COUNTROWS ( FILTER ( { "f", "g" }, SEARCH ( [Value], Table1[col1],, 0 ) > 0 ) ) > 0, "outcome2",
    ""
)

Please mark the resolved question when you are finished and consider giving a thumbs up if the posts are useful.

Contact me privately for assistance with any large-scale BI needs, tutoring, etc.

Bless you