cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
edsonlf
Frequent Visitor

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 @edsonlf ,

 

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 @edsonlf ,

 

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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors