This reference lists 22 DAX text functions, with frequently used functions marked 🔥. Each function includes syntax, an example and parameter notes.
| Function | Description | Syntax | Example |
|---|---|---|---|
BLANK 🔥 |
Returns a blank value | BLANK() |
IF(ISBLANK(Sales[Amount]), BLANK(), Sales[Amount]) |
CONCATENATE 🔥 |
Joins two text strings | CONCATENATE(<text1>, <text2>) |
CONCATENATE("Hello", "World") |
CONCATENATEX 🔥 |
Concatenates text values over a table expression | CONCATENATEX(<table>, <expression>, [<delimiter>]) |
CONCATENATEX(Products, Products[ProductName], ", ") |
FORMAT 🔥 |
Formats a value according to a format string | FORMAT(<value>, <format_string>) |
FORMAT(1234.5, "$#,##0.00") |
LEFT 🔥 |
Returns characters from the start of a string | LEFT(<text>, <num_chars>) |
LEFT("Hello World", 5) |
MID 🔥 |
Returns characters from the middle of a string | MID(<text>, <start_num>, <num_chars>) |
MID("Hello World", 7, 5) |
RIGHT 🔥 |
Returns characters from the end of a string | RIGHT(<text>, <num_chars>) |
RIGHT("Hello World", 5) |
EXACT |
Checks whether two text strings are exactly the same (case-sensitive) | EXACT(<text1>, <text2>) |
EXACT("Hello", "hello") |
FIND |
Returns the start position of one text string inside another (case-sensitive) | FIND(<find_text>, <within_text>, [<start_num>]) |
FIND("World", "Hello World") |
FIXED |
Rounds a number to the given decimals and returns it as text | FIXED(<number>, <decimals>, [<no_commas>]) |
FIXED(1234.567, 1) |
LEN |
Returns the number of characters in a string | LEN(<text>) |
LEN("Hello") |
LOWER |
Converts text to lowercase | LOWER(<text>) |
LOWER("HELLO") |
PROPER |
Capitalizes the first letter of every word in the text | PROPER(<text>) |
PROPER("hello world") |
REPLACE |
Replaces text based on position and length | REPLACE(<old_text>, <start_num>, <num_chars>, <new_text>) |
REPLACE("Hello World", 7, 5, "DAX") |
REPT |
Repeats text a given number of times | REPT(<text>, <number_of_times>) |
REPT("*", 5) |
SEARCH |
Returns the start position of one text string inside another (case-insensitive) | SEARCH(<find_text>, <within_text>, [<start_num>]) |
SEARCH("World", "Hello World") |
SUBSTITUTE |
Replaces existing text with new text in a string | SUBSTITUTE(<text>, <old_text>, <new_text>) |
SUBSTITUTE("Hello World", "World", "DAX") |
TRIM |
Removes all spaces from text except single spaces between words | TRIM(<text>) |
TRIM(" Hello World ") |
UNICHAR |
Returns the Unicode character for a given numeric code | UNICHAR(<number>) |
UNICHAR(65) |
UNICODE |
Returns the numeric code of the first character of the text | UNICODE(<text>) |
UNICODE("A") |
UPPER |
Converts text to uppercase | UPPER(<text>) |
UPPER("hello") |
VALUE |
Converts a text string to a number | VALUE(<text>) |
VALUE("123") |
Detailed parameters for frequently used functions
BLANK
Returns a blank value.
| Parameter | Description |
|---|
CONCATENATE
Joins two text strings.
| Parameter | Description |
|---|---|
text1 |
The first text string to join. |
text2 |
The second text string to join. |
CONCATENATEX
Concatenates text values over a table expression.
| Parameter | Description |
|---|---|
table |
The table (or table expression) to iterate. |
expression |
The expression to concatenate into a single text string. |
delimiter |
(Optional) The separator between concatenated values. |
FORMAT
Formats a value according to a format string.
| Parameter | Description |
|---|---|
value |
The value to format (a number or date). |
format_string |
The format string, e.g. "YYYY-MM-DD" or "$#,##0.00". |
LEFT
Returns characters from the start of a string.
| Parameter | Description |
|---|---|
text |
The text string to extract characters from. |
num_chars |
The number of characters to return. 0 or negative returns an empty string. |
MID
Returns characters from the middle of a string.
| Parameter | Description |
|---|---|
text |
The text string to extract characters from. |
start_num |
The position to start extracting from (1-based). |
num_chars |
The number of characters to return. |
RIGHT
Returns characters from the end of a string.
| Parameter | Description |
|---|---|
text |
The text string to extract characters from. |
num_chars |
The number of characters to return. 0 or negative returns an empty string. |
From Online Toolbox (www.vba.net)