Calculated Columns: split by deliminator "value1 - value 2 - "

[michellepace]

Good evening, 

Could someone please help me with some nice clean DAX for each of the subsequent columns listed below? 

Many thanks
Michelle

RAW

Split This
Just one value
Sometimes - I have two
And when I am - very lucky - then I can have three

And I can also - have a blank row - like in the above

[camargos88]

@michellepace ,

Try these calculated columns:

Value 1 = 
VAR _index1 = SEARCH("-", 'Table'[Split], 1, 0)
RETURN TRIM(IF(_index1 > 0, MID('Table'[Split], 1, _index1 - 1), 'Table'[Split]))

Value 2 = 
VAR _index1 = SEARCH("-", 'Table'[Split], 1, 0)
VAR _index2 = IF(_index1 > 0, SEARCH("-", 'Table'[Split], _index1 + 1, LEN('Table'[Split]) +1), 0)
RETURN TRIM(IF(_index2 > 0, MID('Table'[Split], _index1 + 1, _index2 - _index1 - 1), BLANK()))

Value 3 = 
VAR _index1 = SEARCH("-", 'Table'[Split], 1, 0)
VAR _index2 = IF(_index1 > 0, SEARCH("-", 'Table'[Split], _index1 + 1, 0), 0)
VAR _index3 = IF(_index2 > 0, SEARCH("-", 'Table'[Split], _index2, 0), 0)
RETURN TRIM(IF(_index3 > 0, MID('Table'[Split], _index2 + 1, LEN('Table'[Split]) - _index3), BLANK()))

[michellepace]

Thankyou for your reply. Unfortunately I do not have the option of using power query. Only a calculated column via Dax 

[AlB]

Hi @michellepace 

This is best done in Power Query. Just use the Split Column feature,  By delimiter, and choose "-" as delimiter

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

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

Cheers