DAX Text Functions
Text functions allow you to manipulate, extract, and combine text (string) values in your data model.
CONCATENATE()
Joins two text strings into one.
Syntax: CONCATENATE(Text1, Text2)
Example:
Full Name = CONCATENATE(Employees[FirstName], CONCATENATE(" ", Employees[LastName]))Simpler Alternative — using & operator:
Full Name = Employees[FirstName] & " " & Employees[LastName]CONCATENATEX()
Concatenates values from a column across all rows, with a delimiter.
Syntax: CONCATENATEX(Table, Expression, Delimiter)
Example:
Product List = CONCATENATEX(Products, Products[ProductName], ", ")Result: "Laptop, Mouse, Keyboard, Monitor"
LEFT()
Extracts a specified number of characters from the start (left side) of a text string.
Syntax: LEFT(Text, NumberOfCharacters)
Example:
Area Code = LEFT(Customers[Phone], 3)"9876543210" → "987"
RIGHT()
Extracts a specified number of characters from the end (right side) of a text string.
Syntax: RIGHT(Text, NumberOfCharacters)
Example:
Last 4 Digits = RIGHT(Customers[Phone], 4)"9876543210" → "3210"
MID()
Extracts characters from the middle of a text string, starting at a specified position.
Syntax: MID(Text, StartPosition, NumberOfCharacters)
Example:
Middle Part = MID(Products[SKU], 4, 5)"ABC12345XYZ" → "12345"
Other Useful Text Functions
| Function | Description | Example |
|---|---|---|
LEN() | Returns the length of a string | LEN("Hello") → 5 |
UPPER() | Converts text to uppercase | UPPER("hello") → "HELLO" |
LOWER() | Converts text to lowercase | LOWER("HELLO") → "hello" |
TRIM() | Removes leading/trailing spaces | TRIM(" Hi ") → "Hi" |
SUBSTITUTE() | Replaces occurrences of a substring | SUBSTITUTE("ABC", "B", "X") → "AXC" |
REPLACE() | Replaces characters at a specific position | REPLACE("ABCDE", 2, 3, "XY") → "AXYE" |
SEARCH() | Finds position of a substring (case-insensitive) | SEARCH("world", "Hello World") → 7 |
FIND() | Finds position of a substring (case-sensitive) | FIND("W", "Hello World") → 7 |
FORMAT() | Formats a value as text | FORMAT(1234.5, "#,##0.00") → "1,234.50" |
BLANK() | Returns a blank value | Used in IF() for empty results |
Practical Example: Formatting Display Values
Sales Display =
FORMAT([Total Sales], "$#,##0") & " (" &
FORMAT([Sales Growth], "0.0%") & " growth)"Result: "$125,000 (12.5% growth)"