topic Calculated Columns: split by deliminator "value1 - value 2 - " in DAX Commands and Tips

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

michellepace — Mon, 30 Nov 2020 19:25:44 GMT

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

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

AlB — Mon, 30 Nov 2020 20:48:12 GMT

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

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

michellepace — Sun, 06 Dec 2020 09:39:22 GMT

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

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

camargos88 — Mon, 07 Dec 2020 18:53:43 GMT

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()))