SUBSTITUTE

SUBSTITUTE is a DAX function that replaces existing text with new text within a text string.

Syntax

SUBSTITUTE(
   Text,
   OldText,
   NewText,
   InstanceNumber
)

Argument	Properties	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

A string of text.

Remarks

• Use the SUBSTITUTE function when you want to replace specific text in a text string; use the REPLACE function when you want 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 old_text, SUBSTITUTE will not replace the text.

• This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

The following formula creates a copy of the column [Product Code] that substitutes the new product code NW for the old product code PA wherever it occurs in the column.

= SUBSTITUTE([Product Code], "NW", "PA")

Related functions

Other functions related to SUBSTITUTE are:

• REPLACE
• TRIM

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/dax/substitute-function-dax