REPLACE function is a Power BI text function in DAX, which replaces part of a text string based on the number of characters you specify, with a different text string.
SYNTAX
REPLACE(<old_text>, <start_num>, <num_chars>, <new_text>)
| old_text | is the string text that contains the characters you want to replace, or a reference to a column that contains text. |
| start_num | is the position of the character in old_text that you want to replace with new_text. |
| num_chars | is the number of characters that you want to replace. |
| new_text | The replacement text for the specified characters in old_text. |
Lets look at an example of using REPLACE function in Power BI.
Assume that, you have a text string “Hello, How are you ?”, and you want to replace the text “hello” with “Hi” within string “Hello, How are you ?”.
Following DAX, uses the REPLACE function which replace first five characters of the string that is “Hello” with a new two letter text “Hi”.
REPLACE_Text = REPLACE(“Hello, How are you ?”,1, 5, “Hi”)
Once you commit the DAX, lets drag the DAX measure to the card visual to see the output.
You can see, it replaces the text “Hello” which contains five characters with a new text “Hi” which contains two characters. So before the string was “Hello, How are you ?”, now the string is “Hi, How are you ?”.
Lets look at one more example, here we have a sample dataset as shown below.
| Name | Gender |
| XX Rohit | M |
| XX Mohit Singh | M |
| XX Mahesh | M |
| XXX Dolly | F |
| XXX Diana | F |
| XXX Shurti | F |
| XX David | M |
Lets replace a first two (XX) or first three (XXX) characters of name in dataset with “Mr.” or “Mrs.” based on gender.
[Also Read: SUBSTITUE DAX function]
For this, create a calculated column which uses the REPLACE function Dax as following.
Replace_Text =
VAR For_M =
REPLACE ( Employee[Name], 1, 2, "Mr." )
VAR For_F =
REPLACE ( Employee[Name], 1, 3, "Mrs." )
RETURN
IF ( Employee[Gender] == "M", For_M, For_F )
Lets commit the DAX, and go to Data view page to see the output of DAX.
You can see, for all males the first two characters within name are replaced with “Mr.” and for all females the first three characters are replaced with “Mrs.”.
Also Read..
