What Are DAX Text Functions in Power BI?
DAX text functions — CONCATENATE(), LEFT(), RIGHT(), UPPER(), and FORMAT() — manipulate and format text strings, letting you combine, trim, or reformat column values inside a calculated column or measure. They’re essential whenever raw data needs to be cleaned, standardized, or turned into readable labels for reports and visuals.
Common DAX Text Functions
- CONCATENATE(): joins two text values into one string.
- LEFT(): returns a specified number of characters from the start of a text string.
- RIGHT(): returns a specified number of characters from the end of a text string.
- MID(): returns characters from the middle of a text string, given a starting position.
- UPPER() / LOWER() / PROPER(): convert a text string to uppercase, lowercase, or title case.
- TRIM(): removes extra spaces from text, leaving single spaces between words.
- SUBSTITUTE(): replaces existing text with new text, based on matching content.
- FIND() / SEARCH(): return the starting position of one text string within another.
- FORMAT(): converts a number or date into text using a specified format pattern.
- LEN(): returns the number of characters in a text string.
Formatting Numbers and Dates as Text with FORMAT()
FORMAT() is especially useful for building custom labels inside a visual — for example, FORMAT([Total Sales], “$#,##0”) turns a raw number into a ready-to-display currency string, or FORMAT([OrderDate], “MMM YYYY”) turns a date into a short month-year label.
Extracting Parts of a Text String with LEFT(), RIGHT(), and MID()
When data arrives in coded formats — like customer IDs, invoice numbers, or SKUs — LEFT(), RIGHT(), and MID() let you pull out just the piece you need. For example, MID([InvoiceCode], 4, 4) can extract a 4-digit year embedded inside a longer invoice code, without altering the original column.
Cleaning and Standardizing Text with TRIM(), UPPER(), and PROPER()
Imported data is rarely consistent — stray spaces, inconsistent capitalization, or mixed-case entries are common. TRIM() strips out extra whitespace, while UPPER(), LOWER(), and PROPER() standardize casing so values like “john SMITH ” and “John Smith” are treated — and displayed — the same way.
Joining Text with CONCATENATE() and the & Operator
CONCATENATE() joins two text values, but for combining more than two strings, the & operator is simpler and more flexible:
FullName = Customers[FirstName] & " " & Customers[LastName]
For joining text across multiple rows of a table (rather than just columns), CONCATENATEX() is used instead — commonly seen when building a comma-separated list of selected items in a card visual.
Finding and Replacing Text with FIND(), SEARCH(), and SUBSTITUTE()
FIND() and SEARCH() locate the position of a character or substring within text — useful for splitting data, such as pulling a domain name out of an email address. SUBSTITUTE() then replaces matched text with new text, which is often used to clean up formatting inconsistencies, like removing dashes from phone numbers.
Why These Functions Matter
Mastering DAX text functions makes it possible to turn messy, inconsistent source data into clean, readable report elements — from dynamic titles that update based on slicer selections, to standardized customer names, to properly formatted currency and date labels throughout a Power BI report.
