substitute multiple values

[Anonymous]

Hi I would like to substistute multiple values with one value,

In Excel it can be done by =Substitute(Cell reference,{"Trust","Foundation","Endownment"},"") , with the operator { } it allows you to key in multiple values.

How can I do it in DAX?

Thank you in advance,

Eric

[v-haibl-msft · Accepted solution]

@Anonymous

Please try with nested SUBSTITUTE function with similar DAX formula as below.

Column1_1 = 
SUBSTITUTE (
    SUBSTITUTE (
        SUBSTITUTE ( 'Table'[Column1], "Trust", "One" ),
        "Foundation",
        "One"
    ),
    "Endownment",
    "One"
)

Best Regards,

Herbert

[v-haibl-msft · Accepted solution]

@Anonymous

Please try with nested SUBSTITUTE function with similar DAX formula as below.

Column1_1 = 
SUBSTITUTE (
    SUBSTITUTE (
        SUBSTITUTE ( 'Table'[Column1], "Trust", "One" ),
        "Foundation",
        "One"
    ),
    "Endownment",
    "One"
)

Best Regards,

Herbert

[ImkeF · Accepted solution]

If you're looking for a dynamic approach I think you need to return to the Query editor and M like this:

http://www.thebiccountant.com/2016/05/22/multiple-replacements-in-power-bi-and-power-query/

[Anonymous]

Hi all, thank you so much for helping. I want to accept everyone's reply as solution but Microsoft only allow one solution.

After looking into the solutions using M, I think it is a better solution rather using Dax if there is complex replacement involve.

[ankitpatira]

@Anonymous You can do it using DAX but it is not the only way. Easy way is to use Replace Values function in power bi via query editor. It will replace all occurences but obviously you have to do it few times for each string you want to replace. You can also use Conditional Column feature where you can specify multiple conditions (for multiple strings) and it will result in new column.

[Anonymous]

Hi ankiipatira,

Can you show me the code for multiple value replacement?

However I am still interested what is the operator for DAX so allow easy replacement for the function.