Wildcards with IF in DAX

[Anonymous]

Hello All,

I have a table like this in Excel where column "B" is bringing "TRA" everytime it finds that word within the codes in column "A"... no matter where "TRA" is positioned in the word.

For doing so, i'm using ISNUMBER + SEARCH formulas and works like a charm. Can you guys please help me replicate this in DAX??

This is the formula in cell B2: =IF(ISNUMBER(SEARCH($B$1;A2));"TRA";"")

Thanks!

Edson

[Anonymous]

I would do it in Power Query. First, it's the tool designed for this kind of manipulation. Second, processing be faster. Third, you'll get the best compression and hence fastest DAX. If the table you want to do it on is BIG, Power Query is your only option (unless you can push the calc to your source system).

[harshnathani · Accepted solution]

Hi @Anonymous ,

You can do it in many ways.

Some listed

Incase of M create a Conditional Column

In DAX you can create a new Column

DAX COLUMN 1 =
IF (
    SEARCH (
        "TRA",
        'Table'[String],
        1,
        0
    ) > 0,
    "TRA",
    BLANK ()
)

DAX Column = IF (CONTAINSSTRING('Table'[String], "TRA"), "TRA" ,BLANK())

Regards,
Harsh Nathani
Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[amitchandak]

@Anonymous , You can create a new column

if(CONTAINSSTRING([codes], "TRA") ,"TRA",blank())

https://docs.microsoft.com/en-us/dax/containsstring-function-dax

Other is

https://docs.microsoft.com/en-us/dax/search-function-dax

https://docs.microsoft.com/en-us/dax/find-function-dax

[lbendlin]

Same thing in DAX . Use FIND()  (case sensitive) or SEARCH() (not case sensitive) and handle the result the same way.