The DAX Function SUBSTITUTE can be treated like the Replace function in MSSQL Server. It replaces a string with another string. Its syntax is also similar to the syntax of the Replace function in SQL Server. If we look at the example from the previous post we can see that CONCATENATE has resulted in the values as shown below.
Let us assume we now want to replace the word North with N.
We can achieve this by simple add the SUBSTITUTE Command to the CONCATENATE command as shown below
Fulltext = SUBSTITUTE (CONCATENATE (CONCATENATE('Dimension City'[State Province],"|"),'Dimension City'[City]),"North","N.")
The end result is as shown below
It can be useful especially when removing Currency symbols from Columns which need to be converted to DOUBLE later on.
The SQL equivalent of the above function would be