DAX Text - SUBSTITUTE function


Advertisements

Description

Replaces the existing text with a new text in a text string.

Syntax

SUBSTITUTE (<text>, <old_text>, <new_text>, <instance_num>) 

Parameters

Sr.No. Parameter & Description
1

text

The text in which you want to substitute the existing text with a new text, or a reference to a column containing text.

2

old_text

The existing text that you want to replace.

3

new_text

The text you want to replace old_text with.

4

instance_num

Optional.

The occurrence of old_text you want to replace.

If omitted, every instance of old_text is replaced.

Return Value

A string of text.

Remarks

SUBSTITUTE function is case-sensitive. If the case does not match between find_text and old_text, SUBSTITUTE will not replace the text.

  • If find_text is “Not” and within_text contains “not”, SUBSTITUTE will not replace the text.

DAX SUBSTITUTE function is similar to DAX REPLACE function.

  • You can use SUBSTITUTE function if you want to replace the specific text in a text string.

  • You can use REPLACE function, if you want to replace any text of variable length that occurs at a specific position in a text string.

Example

= SUBSTITUTE([Product],"Powder","Lotion") 

replaces all instances of “Powder” with “Lotion” in the Product column. If “Powder” is not found in any of the rows of the Product column, nothing is changed.

dax_functions_text.htm

Useful Video Courses


Video

Mastering DAX and Data Models in Power BI Desktop

53 Lectures 5.5 hours

Abhay Gadiya

Video

Mastering DAX Studio

Featured

24 Lectures 2 hours

Randy Minder

Video

DAX / Power BI - Customer and Sales Analysis Deep Dive

26 Lectures 4.5 hours

Randy Minder

Advertisements