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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

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! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.