I need help with a DAX formula

[Marquian]

Hello,

Below is a picture of my raw data. I need to delineate the column into rows based on DTP or TRA.

In Excel I use the following formula, =IFERROR(LEFT(SUBSTITUTE([@[Work volume_9]],IFERROR(LEFT([@[Work volume_9]],SEARCH("DTP",[@[Work volume_9]])+4),""),""),SEARCH("Words",SUBSTITUTE([@[Work volume_9]],IFERROR(LEFT([@[Work volume_9]],SEARCH("DTP",[@[Work volume_9]])+4),""),""))-2)*1,"")

which results in 

 

How do I replicate this in PowerBi? It can be a measure, but I prefer to have that data in columns.

Thanks for your help. 

[nabandla]

Column =
VAR dtp_position = FIND("DTP", 'Table'[Work volume_9], 1, -1) + 4
VAR substitute_string = SUBSTITUTE('Table'[Work volume_9], LEFT('Table'[Work volume_9], IF(dtp_position = -1, 0, dtp_position)), "")
VAR words_position = FIND("Words", substitute_string, 1, -1) - 2
RETURN IFERROR(LEFT(substitute_string, IF(words_position = -1, LEN(substitute_string), words_position)), "")

In this formula:

• 'Table' should be replaced with the name of your table in Power BI.
• 'Work volume_9' should be replaced with the name of the column in your Power BI table that corresponds to 'Work volume_9' in your Excel table.
• The -1 in the FIND function calls is used to return an error if the search string is not found (which is then caught by the IF function to return 0 or the length of the string as appropriate).
• The IFERROR function in DAX only takes one argument, so if an error occurs, it will return a blank value. If you want it to return something else (like an empty string), you can add a second argument with that value.

[Marquian]

This was almost perfect. However, I need the number that proceeds DTP as the output. For example, If the column contains "36 words DTP, 45 words TRA", I would like the columns to read 

DTP	TRA
36	45

[nabandla]

Column =
VAR dtp_position = FIND("DTP", 'Table'[Work volume_9], 1, -1) + 4
VAR substitute_string = SUBSTITUTE('Table'[Work volume_9], LEFT('Table'[Work volume_9], IF(dtp_position = -1, 0, dtp_position)), "")
VAR words_position = FIND("Words", substitute_string, 1, -1) - 2
VAR number_string = SUBSTITUTE(SUBSTITUTE(substitute_string, "words", ""), " ", "")
RETURN IFERROR(VALUE(number_string), BLANK())

[Marquian]

This one gave all blanks...

Did I edit it correctly?

Column =

VAR dtp_position = FIND("DTP", 'Jobs'[Work volume_9], 1, -1) + 4

VAR substitute_string = SUBSTITUTE('Jobs'[Work volume_9], LEFT('Jobs'[Work volume_9], IF(dtp_position = -1, 0, dtp_position)), "")

VAR words_position = FIND("Words", substitute_string, 1, -1) - 2

VAR number_string = SUBSTITUTE(SUBSTITUTE(substitute_string, "words", ""), " ", "")

RETURN IFERROR(VALUE(number_string), BLANK())