Home » DAX » DAX – SUBSTITUTE Function

DAX – SUBSTITUTE Function

SUBSTITUTE DAX function

Replaces existing text with new text in a text string. It’s comes under Text functions DAX category.

Syntax:

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




Description:

S no. Parameter Description
1 text The text in which you want to substitute characters, 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

Note:

  • It 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 Direct Query mode when used in calculated columns or row-level security (RLS) rules.




So, Let’s start with an example,  you can download the sample Dataset from below link

Step-1: Create one calculated column, right click to dataset name > select New column

Step-2: Now replace existing “&” text with “and” in column “Product Sub-Category”.

Substitute = SUBSTITUTE(Orders[Product Sub-Category], "&", "and")





Step-3: Drag new column to Table visual

Substitute

So you can see the output of substitute DAX as in above image, it replace “&” text with “and”.




Hope you enjoyed the post. Your valuable feedback, question, or comments about this post are always welcome or you can leave us message on our contact form , we will revert to you asap.

Leave a Reply