cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TeeTreeThree Regular Visitor
Regular Visitor

substitute multiple values

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
v-haibl-msft Super Contributor
Super Contributor

Re: substitute multiple values

@TeeTreeThree

 

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"
)

substitute multiple values_1.jpg

 

Best Regards,

Herbert

Super User
Super User

Re: substitute multiple values

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/

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




5 REPLIES 5
Highlighted
Super User
Super User

Re: substitute multiple values

@TeeTreeThree 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.

 

Capture.PNG

 

 

 

 

 

 

 

 

Capture2.PNG

TeeTreeThree Regular Visitor
Regular Visitor

Re: substitute multiple values

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.

v-haibl-msft Super Contributor
Super Contributor

Re: substitute multiple values

@TeeTreeThree

 

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"
)

substitute multiple values_1.jpg

 

Best Regards,

Herbert

Super User
Super User

Re: substitute multiple values

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/

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




TeeTreeThree Regular Visitor
Regular Visitor

Re: substitute multiple values

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.