SUBSTITUE Function DAX
SUBSTITUE function is a Power BI DAX TEXT function that allows to replace existing text with new text in a text string.
The SUBSTITUTE function is case-sensitive.
DAX Syntax
SUBSTITUTE(<text>, <old_text>, <new_text>, <instance_num>)
text is the text in which you want to substitute characters, or a reference to a column containing text.
old_text is the existing text that you want to replace.
new_text is the text you want to replace old_text with.
instance_num It is an optional, The occurrence of old_text you want to replace. If it is not provided, every instance of old_text is replaced.
Lets see an example of SUBSTITUE function in Power BI.
Following DAX Substitue_Measure uses a substitue function, that replaces a text “NO” to “YES” wherever it occurs within a string “NO MEANS NO”.
Substitue_Measure = SUBSTITUTE( "NO MEANS NO", "NO", "YES")
Lets see the output of DAX, drag the Measure to Card visual, You can see, Text “NO” Within a string “NO MEANS NO” is Replaced with “YES”.
Lets say, If you want to replace only the first occurrence of text “NO” to “YES” within a string “NO MEANS NO” then you have to provide the value for fourth paramter <instance_num> in SUBSTITUE function.
As you want to replace only first occurrence of text within a string so you need to pass fourth parameter value as 1, so SUBSITUTE function will replace only first occurrence of text within a string and leave other occurrence if any.
Lets modify the above DAX function as given below.
Substitue_Measure = SUBSTITUTE( "NO MEANS NO", "NO", "YES", 1)
Lets see the output of measure, You can see the only first occurrence of text “NO” is replaced with “YES”.
SUBSTITUE Function is CASE SENSITIVEÂ
Substitute function is case sensitive, It means text “A” is not same as “a”.
Here we have a string ABCDabcd, In this string we have two a’s, one is in upper case “A” and other is lower case “a”.
So if you want to replace all “a” with “e” within string then SUBSTITUE function only replace all “a” within string that is in lower letter not “A”.
Lets create a DAX function, that replaces “a” with “e”.
Substitue_Measure = SUBSTITUTE( "ABCDabcd", "a", "e")
Lets see the output of DAX, you can see first a is not affected that was in upper case while other “a” is replaced with “e”
