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

Dax - Left where position is a find result less 1 position

Hi, i have a field that has a seperator "-" 2 times , Example :

4124124-RET-2242d

trtre121-1t5-123213

 

I´m new here, i get error on this, for the first split.

NewColumn = Lef(Field, find("-",Field,1,)-1)

if i try the Lef(Field, find("-",Field,1,)1)  - Positive number it works)

 

Thanks.

2 ACCEPTED SOLUTIONS
PhilipTreacy
Super User
Super User

Hi @joaofigu 

OK so what are you actually trying to do?

Please provide enough sample data to illustrate the problems you are facing.  You've supplied 2 examples of strings that contain - but then said not all of the strings contain -

If there is no - then SEARCH returns BLANK and that's an invalid agument for LEFT

Please explain what your expected results are.

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

viviank
Resolver I
Resolver I

This is easy in the Query Editor.

Go to the Home tab, choose Split Column by Delimiter (under Transform), use a Custom Delimiter, choose your - sign and choose to split at "Each occurrence of the delimiter".

 

(I know you want the DAX answer, but if you can do it in Query Editor, it's easier and it processes faster.)

View solution in original post

5 REPLIES 5
viviank
Resolver I
Resolver I

This is easy in the Query Editor.

Go to the Home tab, choose Split Column by Delimiter (under Transform), use a Custom Delimiter, choose your - sign and choose to split at "Each occurrence of the delimiter".

 

(I know you want the DAX answer, but if you can do it in Query Editor, it's easier and it processes faster.)

PhilipTreacy
Super User
Super User

Hi @joaofigu 

OK so what are you actually trying to do?

Please provide enough sample data to illustrate the problems you are facing.  You've supplied 2 examples of strings that contain - but then said not all of the strings contain -

If there is no - then SEARCH returns BLANK and that's an invalid agument for LEFT

Please explain what your expected results are.

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


PhilipTreacy
Super User
Super User

Hi @joaofigu 

What exactly are you trying to do?

If you want to get all the characters before the first - then use this 

 

Column = LEFT([Column1],FIND("-",[Column1])-1)

 

left1.png

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Error descrp: An argument of the 'LEFT' function has the wrong data type or an invalid value.

column = LEFT(cvg[Column1],SEARCH("-",[Column1],1,BLANK())-1)

 

some lines don't have the "-", so I put the rest of the find form, starting position (1) and if there is no "-" (Blank)

Hello and thank you all, and sorry for not mentioning that some lines do not have the seperator. but it worked in the power editor, I tried to do it again in dax for several columns, in a first try to get the position even for those who didn't have it (it got -1). and then lelft will fetch the number of characters in that column but the left always gives an error.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors