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

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
Anonymous
Not applicable

Subtracting one character from SEARCH result

I have a source column called Defect1Code which can contain any one of dozens of codes that follow this structure: G5.4.1 – Some Text Here. The length of the alphanumeric at the beginning varies, and in some cases the column for a given row may be blank altogether. All are acceptable possibilities.

 

I want to extract the alphanumeric out, so I am using this DAX:

Column = LEFT ([Defect1Code], (SEARCH (" ", [Defect1Code],, BLANK()))

 

This seems to work perfectly, but with one itty-bitty hitch: from what I can tell it also seems to be bringing over the first space after the last alphanumeric (i.e. “G5.4.1 “ – but without the quotes of course).

 

I thought adding a simple “-1” after the SEARCH would solve it, and it does except (and here’s the big problem) it treats BLANK returns as a ZERO, so in rows with a blank Defect1Code the SEARCH returns -1, which hoses up the LEFT function!

 

Am I missing a better solution?

1 ACCEPTED SOLUTION
AlB
Super User
Super User

Hi @Anonymous 

Column =
VAR pos_ =
    SEARCH ( " ", [Defect1Code],, BLANK () )
RETURN
    IF ( NOT ISBLANK ( pos_ ), LEFT ( [Defect1Code], pos_ - 1 ) )

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

Cheers 

 

SU18_powerbi_badge

View solution in original post

3 REPLIES 3
AlB
Super User
Super User

Hi @Anonymous 

Column =
VAR pos_ =
    SEARCH ( " ", [Defect1Code],, BLANK () )
RETURN
    IF ( NOT ISBLANK ( pos_ ), LEFT ( [Defect1Code], pos_ - 1 ) )

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

Cheers 

 

SU18_powerbi_badge

Anonymous
Not applicable

Awesome - THANKS!

 

MattAllington
Community Champion
Community Champion

I dint think DAX is the best option here. Power Query is built to do these types of things. You should consider cleaning the columns in PQ before you load the data so it works to your needs without using functions like LEFT and SEARCH 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

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

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.