Replace/Substitute text in specific column using DAX

[Anonymous]

I need to replace a text in a specific column, but as my report comes from a cube, I cannot manipulate the data using Power Query or Add Columns, the only option I have is to create a measure using DAX.

I did a research and I could use Replace or Substitute, but I'm still not quite sure how to replace a text every time it appears in the column. Below is an example in excel of what I'm looking for to do in PBI. In this example, I need to substitute/replace Apple for Pear.

Original Names	NEW Names
Apple	Pear
Banana	Banana
Strawberry	Strawberry
Apple	Pear
Banana	Banana
Strawberry	Strawberry
Strawberry	Strawberry
Strawberry	Strawberry
Banana	Banana
Banana	Banana
Apple	Pear
Banana	Banana
Apple	Pear
Apple	Pear
Strawberry	Strawberry
Apple	Pear
Banana	Banana
Strawberry	Strawberry
Strawberry	Strawberry
Strawberry	Strawberry
Banana	Banana
Banana	Banana

I appreciate any help. Thanks!

[Anonymous]

Hello,

Yes, you could use the SUBSTITUTE function but it appears that it only allows one string at a time. If you're facing an issue where you need to replace the string value of multiple rows, I don't think this would work.

What I would do would be to use the SWITCH() and SELECTEDVALUE() functions. Unfortunately it's a very manual solution but if you don't have 100s of words to replace, this could be an effective solution without needing to change the underlying data values.

For example:

NEW Names =
VAR SelectedName = SELECTEDVALUE([Original Name]) //This returns the selected value from original name in the table visual.
RETURN
SWITCH( SelectedName,  //This evaluates true if the 2nd argument is ==, else it evaluates false and continues through the switch
    "Apple", "Pear", 
    "Raspberry", "Blueberry",
    "Cherry", "Watermelon",
    "SelectedName"  //This simply returns the selected name if the name doesn't equal the values listed above
)

[Anonymous]

Thanks for the reply. I tried it but unfortunately, it didn't work. I guess I need to change in the cube.

[Anonymous · Accepted solution]

Whenever your source is live connection you can create only measure from power BI side.

For your requirement it's better replace values at cube side on existing column or by creating new column.

Thanks & regards,
Pravin Wattamwar
www.linkedin.com/in/pravin-p-wattamwar

If I resolve your problem Mark it as a solution and give kudos.

[Anonymous]

Thanks for the reply. I need to change it in the cube indeed.