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

[joaofigu]

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.

[PhilipTreacy · Accepted solution]

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

[viviank · Accepted solution]

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 · Accepted solution]

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

[PhilipTreacy]

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)

Regards

Phil

[joaofigu]

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)

[joaofigu]

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.