DAX: SUBSTITUE Instance not dynamic

[Obrycki]

In Power BI Desktop I'm attempting to dynamically determine the last instance of a character and SUBSTITUTE that character with another. The following DAX formula appears to work in Excel but does not work in Power BI Desktop.

Column1 = test1_test2_test3

Formula: Last_Replaced = Last_Replaced = SUBSTITUTE(table1[Column1],"_","@",LEN(table1[Column1])-LEN(SUBSTITUTE(table1[Column1],"_","")))

This returns "An argument of function 'SUBSTITUTE has the wrong data type or has an invalid value."

However, if I set the number to 2 in the forumla it works.

If I first create a column that determines the value and holds this then include that new column in the formula it fails with the same error. 

Is this by design, a bug, or is there another way to accomplish what I'm trying to do in Power BI Desktop?

Thank you,

Trav

[lbendlin · Accepted solution]

Replace your underscore "_"  with a pipe "|"  and use the PATH functions.