Home » SUBSTITUE Function DAX

SUBSTITUE Function DAX

SUBSTITUE Function DAX




SUBSTITUE function is a Power BI DAX TEXT function that allows to replace existing text with new text in a text string.

The SUBSTITUTE function is case-sensitive.

DAX Syntax

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

text  is the text in which you want to substitute characters, or a reference to a column containing text.

old_text is the existing text that you want to replace.

new_text is the text you want to replace old_text with.

instance_num It is an optional, The occurrence of old_text you want to replace. If it is not provided, every instance of old_text is replaced.

Lets see an example of SUBSTITUE function in Power BI.

Following DAX Substitue_Measure uses a substitue function, that replaces a text “NO” to “YES” wherever it occurs within a string “NO MEANS NO”.

Substitue_Measure = SUBSTITUTE( "NO MEANS NO", "NO", "YES")

 

Lets see the output of DAX, drag the Measure to Card visual, You can see, Text “NO” Within a string “NO MEANS NO” is Replaced with “YES”.

Lets say, If you want to replace only the first occurrence of text “NO” to “YES” within a string “NO MEANS NO” then you have to provide the value for fourth paramter <instance_num> in SUBSTITUE function.

As you want to replace only first occurrence of text within a string so you need to pass fourth parameter value as 1, so SUBSITUTE function will replace only first occurrence of text within a string and leave other occurrence if any.

Lets modify the above DAX function as given below.

Substitue_Measure = SUBSTITUTE( "NO MEANS NO", "NO", "YES", 1)

Lets see the output of measure, You can see the only first occurrence of text “NO” is replaced with “YES”.

SUBSTITUE Function is CASE SENSITIVE 

Substitute function is case sensitive, It means text “A” is not same as “a”.

Here we have a string ABCDabcd, In this string we have two a’s, one is in upper case “A” and other is lower case “a”.

So if you want to replace all “a” with “e” within string then SUBSTITUE function only replace all “a” within string that is in lower letter not “A”.

Lets create a DAX function, that replaces “a” with “e”.

Substitue_Measure = SUBSTITUTE( "ABCDabcd", "a", "e")

 

Lets see the output of DAX, you can see first a is not affected that was in upper case while other “a” is replaced with “e”





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




 745 total views,  1 views today

Leave a Reply

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