Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
v-haibl-msft
Employee
Employee

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

substitute multiple values_1.jpg

 

Best Regards,

Herbert

View solution in original post

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/

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

5 REPLIES 5
v-haibl-msft
Employee
Employee

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

substitute multiple values_1.jpg

 

Best Regards,

Herbert

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/

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Anonymous
Not applicable

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
Community Champion
Community Champion

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

 

Capture.PNG

 

 

 

 

 

 

 

 

Capture2.PNG

Anonymous
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.