Forum Discussion
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
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
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/
5 Replies
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
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 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.
