Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Wildcards with IF in DAX

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";"")

 

edsonlf_1-1597965164224.png

 

Thanks!

Edson

1 ACCEPTED SOLUTION
harshnathani
Community Champion
Community Champion

Hi @Anonymous ,

 

You can do it in many ways.

 

Some listed

 

Incase of M create a Conditional Column

 

2.JPG

 

 

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())

 

 

1.jpg

 

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

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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
Community Champion
Community Champion

Hi @Anonymous ,

 

You can do it in many ways.

 

Some listed

 

Incase of M create a Conditional Column

 

2.JPG

 

 

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())

 

 

1.jpg

 

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

amitchandak
Super User
Super User

lbendlin
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors