Real-world data is messy — inconsistent phone numbers, embedded category codes, date values stored as text. This lesson teaches you how to use DAX's SEARCH, SUBSTITUTE, and FORMAT functions to clean, parse, and categorize string data, with practical examples you can apply immediately in Power BI.
You've just received a dataset from a client. The product codes look like "PROD-2024-ELECTRONICS-001", customer names are a mix of uppercase and lowercase, phone numbers have parentheses, dashes, and spaces scattered everywhere, and dates are stored as text in a format your reporting tool doesn't recognize. Sound familiar? This is real-world data, and it almost never arrives in the shape you need.
Most DAX tutorials focus on aggregation — SUM, AVERAGE, CALCULATE — because that's the flashy part of analytics. But before you can aggregate anything meaningfully, you often need to clean, parse, and categorize the text in your dataset. That's where DAX's text and string functions come in. They're the unglamorous workhorses of data modeling, and once you understand them, you'll wonder how you ever reported on messy data without them.
By the end of this lesson, you'll be able to write calculated columns and measures that locate substrings inside text, replace or remove unwanted characters, reformat dates and numbers for display, and classify records into categories based on what their text contains. These are skills you'll use on almost every real project.
What you'll learn:
SEARCH and FIND locate text within a string, and when to use eachSUBSTITUTE to clean and standardize messy text fieldsFORMAT converts numbers and dates into human-readable stringsIF and ISNUMBER to build category labelsBefore diving in, you should be comfortable with:
IF, LEFT, RIGHT, and LEN (we'll briefly recap them as needed)In DAX, text values are strings — sequences of characters enclosed in double quotes. The number 42 and the text "42" are fundamentally different things. DAX won't let you add a number to a string without an explicit conversion, which is actually a feature: it forces you to be intentional about data types.
Most text functions in DAX operate on individual cell values, making them ideal for calculated columns — columns you add to an existing table that evaluate the formula row by row. Think of a calculated column like adding a new column in Excel and typing a formula, then letting it fill down automatically for every row.
Here's a mental model that will serve you throughout this lesson: text functions let you look inside a string (SEARCH, FIND), modify a string (SUBSTITUTE, TRIM, UPPER, LOWER), or format a value as a string (FORMAT). You'll often chain these together — look inside a string to find something, then modify it based on what you found.
SEARCH answers the question: Where inside this text does a particular substring appear? It returns the position number (starting from 1) of the first character of the match.
The syntax is:
SEARCH(find_text, within_text [, start_position] [, not_found_value])
find_text: The string you're looking for (e.g., "-" or "Electronics")within_text: The string you're searching insidestart_position: Optional. Where to start searching (default is 1)not_found_value: Optional. What to return if the text isn't found. If you omit this and the text isn't found, DAX throws an error.Let's say you have a table called Products with a column [ProductCode] containing values like "PROD-2024-ELECTRONICS-001". You want to find where the first dash appears.
FirstDashPosition = SEARCH("-", Products[ProductCode], 1, 0)
For the value "PROD-2024-ELECTRONICS-001", this returns 5 because the first dash is the 5th character. The 0 at the end is our safety net — if no dash is found, return 0 instead of crashing.
FIND has identical syntax to SEARCH but with one critical difference: FIND is case-sensitive, SEARCH is not.
-- SEARCH: finds "electronics" in "PROD-2024-ELECTRONICS-001"
SEARCH("electronics", Products[ProductCode], 1, 0) -- Returns 11
-- FIND: returns 0 because "electronics" (lowercase) doesn't match "ELECTRONICS"
FIND("electronics", Products[ProductCode], 1, 0) -- Returns 0
In practice, SEARCH is the right choice most of the time unless you specifically need case-sensitive matching — for instance, distinguishing between product codes that use case to encode meaning ("PRod" vs "PROD").
Position numbers become powerful when you combine them with MID, LEFT, or RIGHT. The MID function extracts characters from any position: MID(text, start_position, number_of_characters).
Imagine you want to extract the category segment from "PROD-2024-ELECTRONICS-001". The category sits between the second and third dashes. Let's find those positions:
SecondDash = SEARCH("-", Products[ProductCode], SEARCH("-", Products[ProductCode], 1, 0) + 1, 0)
This finds the second dash by starting the search one position after the first dash. With both positions in hand, you can extract the category:
CategoryExtracted =
VAR FirstDash = SEARCH("-", Products[ProductCode], 1, 0)
VAR SecondDash = SEARCH("-", Products[ProductCode], FirstDash + 1, 0)
VAR ThirdDash = SEARCH("-", Products[ProductCode], SecondDash + 1, 0)
RETURN
MID(Products[ProductCode], SecondDash + 1, ThirdDash - SecondDash - 1)
For "PROD-2024-ELECTRONICS-001", this returns "ELECTRONICS". The VAR keyword lets you store intermediate calculations — think of them as named scratch-pad values that make complex formulas readable.
Tip: Using
VARto break complex text parsing into steps isn't just good style — it makes debugging much easier. You can temporarily change theRETURNstatement to return an intermediate variable to inspect its value.
SUBSTITUTE finds every occurrence of one substring and replaces it with another. The syntax is:
SUBSTITUTE(text, old_text, new_text [, instance_num])
text: The original stringold_text: The substring to find and replacenew_text: What to replace it withinstance_num: Optional. Which occurrence to replace (if omitted, all occurrences are replaced)The classic use case is removing unwanted characters. Suppose phone numbers in your Customers table look like "(617) 555-0192". You want a clean numeric string "6175550192" for joining to another table or for validation.
CleanPhone =
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(Customers[PhoneRaw], "(", ""),
")", ""),
" ", ""),
"-", "")
You're nesting four SUBSTITUTE calls, each peeling off one type of unwanted character. Reading from inside out: first remove (, then ), then spaces, then dashes. The result is a clean 10-digit string.
Warning: This inside-out nesting gets hard to read quickly. When you have more than two or three substitutions, consider using
VARto chain them step by step.
The cleaner version using VAR:
CleanPhone =
VAR Step1 = SUBSTITUTE(Customers[PhoneRaw], "(", "")
VAR Step2 = SUBSTITUTE(Step1, ")", "")
VAR Step3 = SUBSTITUTE(Step2, " ", "")
VAR Step4 = SUBSTITUTE(Step3, "-", "")
RETURN Step4
Same result, dramatically more readable.
SUBSTITUTE also handles business logic standardization. Imagine a [Status] column where different data entry operators have used "In-Progress", "In Progress", "in progress", and "INPROGRESS" to mean the same thing. You need them all to become "In Progress" for consistent reporting.
StandardStatus =
VAR Lowered = LOWER(Customers[Status])
VAR NoHyphen = SUBSTITUTE(Lowered, "-", " ")
RETURN
IF(NoHyphen = "in progress", "In Progress",
IF(NoHyphen = "complete", "Complete",
IF(NoHyphen = "pending", "Pending",
"Unknown")))
First you normalize case with LOWER, then remove hyphens with SUBSTITUTE, then map known values. The nested IF at the end is a common pattern for categorical mapping — though for long lists, a separate lookup table is a better architectural choice.
The optional fourth argument of SUBSTITUTE targets a specific occurrence. If your product code format is "BOLT-HEX-M8-200" and you only want to replace the second hyphen (to split the descriptor from the spec), you'd write:
SUBSTITUTE(Products[ProductCode], "-", "|", 2)
This replaces only the second hyphen, yielding "BOLT-HEX|M8-200", leaving the others intact. This is especially useful when a delimiter appears multiple times and you need to split on a specific occurrence.
FORMAT converts a number or date value into a text string using a format pattern. Its syntax is:
FORMAT(value, format_string)
This function has two main applications: making numbers display-ready (adding currency symbols, percentage signs, thousands separators) and making dates display-ready (turning 2024-03-15 into "March 2024" or "Q1 2024").
Important:
FORMATalways returns text, not a number or date. This means you can't use aFORMATresult in a SUM or date comparison. UseFORMATonly for display purposes in report visuals, not for calculations.
-- Turn 1234567.8 into "$1,234,567.80"
FormattedRevenue = FORMAT([TotalRevenue], "$#,##0.00")
-- Turn 0.1875 into "18.75%"
FormattedMargin = FORMAT([GrossMargin], "0.00%")
-- Turn 42000 into "42,000"
FormattedUnits = FORMAT([UnitsSold], "#,##0")
The format string uses placeholders: 0 means "always show this digit position, use 0 if no digit," # means "show this digit only if it exists," and , adds thousands separators. The . sets the decimal position.
This is where FORMAT really earns its keep. Power BI's native date formatting is controlled by visual settings, but when you need to create a text label like a fiscal quarter header or a month-year axis label stored as a column, FORMAT is the tool.
-- Turn a date into "March 2024"
MonthYearLabel = FORMAT(Sales[OrderDate], "MMMM YYYY")
-- Turn a date into "Mar-24"
ShortMonthLabel = FORMAT(Sales[OrderDate], "MMM-YY")
-- Turn a date into "Q1 FY2024" (requires additional logic for fiscal quarters)
MonthNum = FORMAT(Sales[OrderDate], "MM")
For fiscal quarter labeling — a very common business requirement — you'd combine FORMAT with IF or SWITCH:
FiscalQuarter =
VAR MonthNum = MONTH(Sales[OrderDate])
VAR FiscalQ =
SWITCH(TRUE(),
MonthNum >= 10, "Q1",
MonthNum <= 3, "Q1",
MonthNum <= 6, "Q2",
MonthNum <= 9, "Q3",
"Q4")
VAR FiscalYear =
IF(MonthNum >= 10,
YEAR(Sales[OrderDate]) + 1,
YEAR(Sales[OrderDate]))
RETURN FiscalQ & " FY" & FORMAT(FiscalYear, "0000")
For a company with an October fiscal year start, an order placed on November 15, 2023 would produce "Q1 FY2024". The & operator concatenates text strings in DAX.
The real power of text functions emerges when you use them to make decisions. A common pattern: use SEARCH to test whether a string contains a keyword, then use that result to assign a category label.
Because SEARCH returns an error (or your chosen not-found value) when text isn't found, you can wrap it in ISNUMBER to produce a clean TRUE/FALSE result. ISNUMBER returns TRUE if its argument is a number, FALSE otherwise.
IsElectronics =
ISNUMBER(SEARCH("ELECTRONICS", Products[ProductCode], 1))
When "ELECTRONICS" is found, SEARCH returns a number (the position), and ISNUMBER returns TRUE. When it's not found, SEARCH returns an error, and ISNUMBER returns FALSE. Note: here we're omitting the not-found value so SEARCH errors on failure — that's intentional because ISNUMBER handles the error cleanly.
Now you can build a full category column:
ProductCategory =
SWITCH(TRUE(),
ISNUMBER(SEARCH("ELECTRONICS", Products[ProductCode])), "Electronics",
ISNUMBER(SEARCH("FURNITURE", Products[ProductCode])), "Furniture",
ISNUMBER(SEARCH("APPAREL", Products[ProductCode])), "Apparel",
ISNUMBER(SEARCH("GROCERY", Products[ProductCode])), "Grocery",
"Uncategorized")
SWITCH(TRUE(), ...) is a DAX pattern worth memorizing. It evaluates each condition in order and returns the result for the first one that's TRUE. It's cleaner than deeply nested IF statements.
Tip: The order of conditions in
SWITCH(TRUE(), ...)matters. If a product code could contain both"ELECTRONICS"and another keyword, it will be assigned to whichever category appears first in your list. Put your most specific conditions first.
Let's put everything together with a realistic scenario. You've loaded a table called Orders with these columns:
[OrderID]: text like "ORD-2024-NA-00542" (Order-Year-Region-Sequence)[CustomerPhone]: text like "(800) 555-0100" or "800-555-0100" or "8005550100" (inconsistent format)[OrderDate]: a date column[Revenue]: a decimal numberYour tasks:
Step 1: Create a calculated column [OrderRegion] that extracts the region code from [OrderID] (the segment between the 3rd and 4th dash — e.g., "NA" for North America).
OrderRegion =
VAR D1 = SEARCH("-", Orders[OrderID], 1, 0)
VAR D2 = SEARCH("-", Orders[OrderID], D1 + 1, 0)
VAR D3 = SEARCH("-", Orders[OrderID], D2 + 1, 0)
RETURN
MID(Orders[OrderID], D2 + 1, D3 - D2 - 1)
Step 2: Create a calculated column [CleanPhone] that normalizes all phone numbers to 10 digits.
CleanPhone =
VAR S1 = SUBSTITUTE(Orders[CustomerPhone], "(", "")
VAR S2 = SUBSTITUTE(S1, ")", "")
VAR S3 = SUBSTITUTE(S2, "-", "")
VAR S4 = SUBSTITUTE(S3, " ", "")
RETURN S4
Step 3: Create a calculated column [RevenueLabel] that formats revenue for display.
RevenueLabel = FORMAT(Orders[Revenue], "$#,##0.00")
Step 4: Create a calculated column [FiscalPeriod] that shows the fiscal quarter label (assume July fiscal year start).
FiscalPeriod =
VAR M = MONTH(Orders[OrderDate])
VAR FQ =
SWITCH(TRUE(),
M >= 7 && M <= 9, "Q1",
M >= 10 && M <= 12, "Q2",
M >= 1 && M <= 3, "Q3",
"Q4")
VAR FY =
IF(M >= 7, YEAR(Orders[OrderDate]) + 1, YEAR(Orders[OrderDate]))
RETURN FQ & " FY" & FORMAT(FY, "0000")
Try each of these in Power BI Desktop by right-clicking your Orders table in the Fields pane and selecting "New column." Verify the results look correct before moving on.
"My SEARCH formula returns an error for some rows."
You've omitted the not-found value (the 4th argument), and some rows don't contain the search text. Add a fallback: SEARCH("text", [Column], 1, 0). Then decide how to handle zero results downstream.
"SUBSTITUTE didn't replace anything."
The most likely cause is a whitespace issue. Your old_text might have a trailing space, or the source data might use a non-breaking space (character 160) instead of a regular space (character 32). Try SUBSTITUTE([Column], UNICHAR(160), " ") to replace non-breaking spaces first.
"FORMAT turned my date into a number."
Power BI sometimes stores dates as serial numbers internally. If FORMAT([Column], "MMMM YYYY") returns something like "January 1900", your column is actually numeric, not a date. Check the column data type in the Data view — it should be "Date" or "Date/Time", not "Whole Number."
"My SWITCH(TRUE()) category column shows the wrong category."
Remember that SWITCH(TRUE()) returns the first match. If a product code like "ELECTRONICS-FURNITURE-COMBO" exists, it will always match "Electronics" first. Review your data for cases where multiple keywords appear in the same string and decide on a priority order.
"My formatted revenue column won't sum in a visual."
This is expected. FORMAT returns text, and text can't be summed. Keep your original numeric column for calculations and use the formatted column only for display labels when needed. Better yet, use Power BI's built-in "Format" settings on a measure rather than creating a separate column.
You now have a working toolkit for handling messy text in DAX. SEARCH lets you locate substrings and detect whether specific content exists in a field. SUBSTITUTE lets you clean, standardize, and restructure text by swapping characters out. FORMAT gives you control over how numbers and dates appear as text in your reports. And patterns like ISNUMBER(SEARCH(...)) and SWITCH(TRUE(), ...) let you translate all of this into business-relevant categories.
The most important habit to take away: use VAR to decompose complex text operations into named steps. Your future self, and anyone who inherits your data model, will be grateful.
Where to go from here:
TRIM and CLEAN to handle leading/trailing spaces and non-printable characters in imported dataCONCATENATEX to combine text values across multiple rows in a measure contextRELATED and LOOKUPVALUE to bring text values from related tables into your calculated columnsCALCULATE filters — for example, filtering a measure to only rows where [ProductCategory] = "Electronics"Text functions are rarely the star of the show, but they're almost always doing important work behind the scenes. The cleaner and more consistent your text data, the more reliable everything downstream becomes.