SUBSTITUTE DAX Function (Text)
Replaces existing text with new text in a text string.
Syntax
| Parameter | Attributes | Description |
|---|---|---|
| Text |
A string of text, or a reference to a cell containing text, in which you want to substitute characters. |
|
| OldText |
The existing text you want to replace. If the case of old_text does not match the case in the existing text, SUBSTITUTE will not replace the text. |
|
| NewText |
The text you want to replace old_text with. |
|
| InstanceNumber | Optional |
The occurrence of old_text you want to replace. If omitted, every instance of old_text is replaced. |
Return values
The modified string.
Remarks
Use the SUBSTITUTE function to replace specific text in a text string; use the REPLACE function to replace any text of variable length that occurs in a specific location in a text string.
The SUBSTITUTE function is case-sensitive. If case does not match between Text and OldText, SUBSTITUTE will not replace the text.
» 1 related function
Examples
-- SUBSTITUTE replaces a substring in a string with another value.
EVALUATE
CALCULATETABLE (
ADDCOLUMNS (
TOPN ( 5, VALUES ( 'Product'[Product Name] ) ),
"New name",
SUBSTITUTE (
'Product'[Product Name],
"MP3 Player",
"Zune-like player"
)
),
'Product'[Color] IN { "Red", "Blue" }
)
| Product Name | New name |
|---|---|
| Contoso 512MB MP3 Player E51 Blue | Contoso 512MB Zune-like player E51 Blue |
| Contoso 2G MP3 Player E200 Red | Contoso 2G Zune-like player E200 Red |
| Contoso 2G MP3 Player E200 Blue | Contoso 2G Zune-like player E200 Blue |
| Contoso 4GB Flash MP3 Player E401 Blue | Contoso 4GB Flash Zune-like player E401 Blue |
| Contoso 8GB Super-Slim MP3/Video Player M800 Red | Contoso 8GB Super-Slim MP3/Video Player M800 Red |
-- SUBSTITUTE replaces a substring in a string with another value.
EVALUATE
CALCULATETABLE (
ADDCOLUMNS (
TOPN ( 5, VALUES ( 'Product'[Product Name] ) ),
"Hide 2",
SUBSTITUTE (
'Product'[Product Name],
"2", "?"
)
),
'Product'[Color] IN { "Red", "Blue" }
)
| Product Name | Hide 2 |
|---|---|
| Contoso 512MB MP3 Player E51 Blue | Contoso 51?MB MP3 Player E51 Blue |
| Contoso 2G MP3 Player E200 Red | Contoso ?G MP3 Player E?00 Red |
| Contoso 2G MP3 Player E200 Blue | Contoso ?G MP3 Player E?00 Blue |
| Contoso 4GB Flash MP3 Player E401 Blue | Contoso 4GB Flash MP3 Player E401 Blue |
| Contoso 8GB Super-Slim MP3/Video Player M800 Red | Contoso 8GB Super-Slim MP3/Video Player M800 Red |
Related articles
Learn more about SUBSTITUTE in the following articles:
-
From SQL to DAX: String Comparison
In DAX string comparison requires you more attention than in SQL, for several reasons: DAX doesn’t offer the same set of features you have in SQL, a few text comparison functions in DAX are only case-sensitive and others only case-insensitive,… » Read more
Related functions
Other related functions are:
Last update: Nov 14, 2024 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo
Microsoft documentation: https://docs.microsoft.com/en-us/dax/substitute-function-dax