topic DAX - extract numbers from text to a new number column. in DAX Commands and Tips

DAX - extract numbers from text to a new number column.

eilidh3 — Thu, 02 Dec 2021 15:23:44 GMT

Hi all,

I feel this one should be a simple fix but its just not coming to me....

Ive tried LEFT / SWITCH functions but Ive yet to come up with the correct dax function.... I've seen this can be done very quickly within PowerQuery however the table I need to create the new column in was added as a UNION of different columns and my new table isn't showing within PowerQuery.

I would like to show the text column below within a new column as only numbers..... Please note Not Applicable will be equal to 0.

Score Text	Score Number
10 Totally Satisfied	10
5 Neutral	5
1 Totally Dissatisfied	1
Not Applicable	0

Any help, as always, would be appreciated 🙂

THANK YOU!!

Re: DAX - extract numbers from text to a new number column.

smpa01 — Thu, 02 Dec 2021 16:26:43 GMT

eilidh3 does this work for you?

Column = VAR _1 = ADDCOLUMNS ( 'Table', "new", SUBSTITUTE ( 'Table'[Score Text], " ", "|" ) ) VAR _2 = ADDCOLUMNS ( GENERATE ( _1, ADDCOLUMNS ( GENERATESERIES ( 1, PATHLENGTH ( [new] ) ), "_txt", TRIM ( PATHITEM ( [new], [Value], TEXT ) ) ) ), "num", IFERROR ( CONVERT ( [_txt], INTEGER ), 0 ) ) RETURN CALCULATE ( MAXX ( ADDCOLUMNS ( 'Table', "scoreNum", MAXX ( FILTER ( _2, EARLIER ( 'Table'[Score Text] ) = [Score Text] ), [num] ) ), [scoreNum] ) )

Re: DAX - extract numbers from text to a new number column.

Rohan459 — Thu, 01 Dec 2022 16:00:25 GMT

This works a charm! I would like to know how to do it the other way around, but can't seem to figure it out.