Dax Function: SUBSTITUTE
Category: Text Functions
The SUBSTITUTE function in Power BI is a DAX (Data Analysis Expressions) function that replaces occurrences of a specified substring in a text string with another substring. This function is useful for cleaning and standardizing data by replacing unwanted characters, patterns, or text.
Purpose
Text Replacement: Replace specific text or characters in a string with a different value.
Data Standardization: Ensure consistency in text fields by removing or replacing unwanted characters or phrases.
Dynamic Manipulation: Useful in calculated columns and measures to transform text dynamically.
Type of Calculations
The SUBSTITUTE function performs scalar text transformations by replacing specified parts of text strings.
Practical Use Cases
Remove Special Characters: Clean data by removing or replacing unwanted symbols.
Standardize Text: Replace inconsistent variations of a term with a uniform one.
Dynamic Replacements: Change specific values based on logic or requirements in calculated columns or measures.
SUBSTITUTE(<text>, <old_text>, <new_text>, [instance_num])
| Parameter | Type | Description |
|---|---|---|
| text | Text/Scalar | The text or column containing the string where replacements will be made. |
| old_text | Text/Scalar | The substring to be replaced. |
| new_text | Text/Scalar | The substring to replace the old_text. |
| instance_num | Integer (Optional) | Specifies which instance of old_text to replace. If omitted, all occurrences are replaced. |
How Does SUBSTITUTE Dax Function Works
Text Matching: The function looks for exact matches of
old_textin the inputtext.Replacement Logic:
If
instance_numis omitted, all instances ofold_textare replaced withnew_text.If
instance_numis specified, only that particular occurrence is replaced.
Text Construction: The function outputs a modified string based on the replacements.
What Does It Return?
The function returns a text string where the specified replacements have been made.
When Should We Use It?
Data Cleaning: Removing or replacing special characters in imported datasets.
Text Standardization: Ensuring consistent formatting for categorical variables.
Dynamic Reports: Creating user-friendly labels by replacing complex terms in text fields.
Examples
Basic Usage – Replace All Occurrences:
SUBSTITUTE("Power BI is awesome", "awesome", "great")
Result: "Power BI is great"
Specific Instance Replacement:
SUBSTITUTE("Data, Data, Data", "Data", "Info", 2)
Result: "Data, Info, Data" (replaces the second occurrence of “Data”)
Column Usage:
Replace hyphens with spaces in a column:
CleanedText = SUBSTITUTE(Table[Column], "-", " ")
Transforms "Power-BI-Tools" into "Power BI Tools".
Advanced Usage – Combine with LEN:
Replace only the last instance of a substring:
LastInstance = SUBSTITUTE(ReverseString(Text), "Target", "Replace", 1)
(Requires reversing the string beforehand.)
Tips and Tricks
Use Case Sensitivity: Ensure exact matches for
old_text; the function is case-sensitive.Handle Missing Text: Use conditional logic to handle scenarios where
old_textmay not exist.Performance Considerations: Avoid excessive replacements in large datasets; pre-cleaning may improve efficiency.
Performance Impact of SUBSTITUTE DAX Function:
Efficient for Single Replacements: Best for targeted text modifications.
Avoid Complex Nested Calls: Multiple nested SUBSTITUTE calls may degrade performance on large datasets.
Related Functions You Might Need
REPLACE: Replace text based on position rather than matching a substring.
TEXT: Format numbers or text dynamically.
LEN: Get the length of a text string to help with advanced text manipulation.
SEARCH: Locate substrings to conditionally replace them.
Want to Learn More?
For more information, check out the official Microsoft documentation for SUBSTITUTE You can also experiment with this function in your Power BI reports to explore its capabilities.
Unlock the full capabilities of Power BI and elevate your data insights with our specialized consulting services. Whether you need guidance on advanced DAX functions like those highlighted here, support in designing interactive dashboards, or expertise in optimizing data models for enhanced performance, our experienced Power BI consultants are equipped to deliver customized solutions for your business. Explore our Power BI Consulting Services page to discover how we can help your organization make smarter, data-driven decisions.
It replaces specified substrings in a text string with new text.
Yes, the function is case-sensitive. “Text” and “text” are treated as different strings.
Yes, you can use the instance_num parameter to specify which occurrence to replace.
SUBSTITUTE replaces text based on matching substrings, while REPLACE operates based on positions in the text.
If the substring is not found, the original text is returned unchanged.
Established in 2020, Lets Viz Technologies provides a full range of high-quality data analysis and data visualization services. We are also an authorized Zoho Partner.
Sitelinks
