REPLACE DAX Function in Power BI

The REPLACE function in DAX (Data Analysis Expressions) for Power BI is used to substitute a portion of a text string with a different text string, based on the position of the characters.

DAX Syntax REPLACE(text, start_num, num_chars, replacement_text)

The function has the following parameters:

• text: The original text string in which we want to replace characters, or a reference to a column that contains text.
• start_num: The position of the first character in text that we want to replace with replacement_text. The first character in text has a position of 1.
• num_chars: The number of characters that we want to replace.
• replacement_text: The text string that will replace the specified characters.

Example: Let’s create a new calculated column in the Power BI.

DAX

The output of the above dax code is shown below:

The following are the key points of the REPLACE dax function:
• 1-based indexing: DAX uses 1-based indexing, meaning the first character is at position 1, not 0.
• The REPLACE function always returns a text string.
• Fixed position replacement: REPLACE is useful when you know the exact position and length of the text you want to change. If we need to replace text based on its content rather than position, you might want to consider using the SUBSTITUTE function.

Difference between REPLACE and SUBSTITUTE:

• REPLACE: Replaces characters based on their position.
• SUBSTITUTE: Replaces existing text with new text based on the text content itself, not position. It also has an instance parameter that can be used to only replace a specific instance of the text.