Home » REPLACE Function DAX

REPLACE Function DAX

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”)

[Also Read: FIND DAX function]

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..

LEN

UNICHAR

REPT

SUBSTITUE

CONCATENATEX

FIND




SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More

 1,882 total views,  1 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.