Skip to content
Tutorials Center
My learning

Topics

Lesson 29 of 56 ยท DAX

DAX Text Functions: FORMAT, CONCATENATEX, SUBSTITUTE and More

Learn the DAX text functions: LEFT, MID, TRIM, SUBSTITUTE, FIND, SEARCH, FORMAT and CONCATENATEX, with pandas stand-ins that show the exact results.

Most DAX is about numbers, but real models are full of text: product codes that need splitting, names that need tidying, labels such as "Q1 2026" that must be built from a date, and titles that should change when a slicer changes. DAX has a family of text functions for all of this. They look like their Excel cousins, with a few important differences that this lesson points out.

You will learn the functions you will use most, grouped by job: measuring and slicing text, cleaning it, searching it, converting values to text with FORMAT, and gluing many rows into one string with CONCATENATEX. If you are not yet comfortable with where a formula can live, read DAX Basics first.

DAX runs only inside Power BI Desktop, the service and Analysis Services, so it cannot run on this site. The DAX blocks are read-only. The runnable examples are Python stand-ins that reproduce what each function returns. Each section says which part is the stand-in. Remember one real difference: DAX positions start at 1, Python indexes start at 0.

Slicing text: LEN, LEFT, RIGHT, MID

These four work on characters and are the base of most text parsing.

DAX
Code Length   = LEN ( Orders[Code] )
Country Part  = LEFT ( Orders[Code], 2 )
Sequence Part = RIGHT ( Orders[Code], 4 )
Year Part     = MID ( Orders[Code], 4, 4 )

For a code such as PB-2025-0042 the results are 12, PB, 0042 and 2025. MID(text, start, count) is the only one where you give a start position, and the start is 1-based. LEFT and RIGHT default to one character if you leave the count out, which is a common source of silent mistakes.

These functions are usually written in a calculated column or in Power Query, because they work row by row. In a measure you need an iterator or a single value to feed them, as covered in Aggregators and Iterators.

Cleaning text: TRIM, CLEAN, UPPER, LOWER, REPT

DAX
Clean Name = TRIM ( Customers[Name] )
Region Key = UPPER ( TRIM ( Customers[Region] ) )
Divider    = REPT ( "-", 20 )

TRIM removes leading and trailing spaces and collapses runs of spaces between words to one space, exactly like Excel. CLEAN removes non-printing characters such as line breaks. UPPER and LOWER change case. DAX has no PROPER function, so if you need "Title Case" do it in Power Query with Text.Proper, as in Cleaning Data in Power Query.

Tidy text early. A relationship on a key that has a trailing space in one table will silently fail to match, and so will a slicer that shows "North" and "North " as two items. Fixing it in Power Query, before the data reaches the model, is better than patching it with DAX.

Searching: FIND, SEARCH, CONTAINSSTRING

DAX
Dash Position  = FIND ( "-", Orders[Code], 1, 0 )
Has Desktop    = SEARCH ( "desktop", Products[Name], 1, 0 )
Is Pro Product = CONTAINSSTRING ( Products[Name], "pro" )
Function Case sensitive Wildcards If not found
FIND(find, within, [start], [notFound]) yes no error, unless you give notFound
SEARCH(find, within, [start], [notFound]) no ? and * error, unless you give notFound
CONTAINSSTRING(within, find) no no returns FALSE
CONTAINSSTRINGEXACT(within, find) yes no returns FALSE

Always pass the fourth argument (notFound, often 0 or -1) so a missing match does not produce an error in a whole column. CONTAINSSTRING returns TRUE or FALSE, which makes it the best choice inside a filter condition.

Replacing: SUBSTITUTE, REPLACE

These two are easy to confuse.

DAX
Slash Code   = SUBSTITUTE ( Orders[Code], "-", "/" )
Second Dash  = SUBSTITUTE ( Orders[Code], "-", "/", 2 )
New Year     = REPLACE ( Orders[Code], 4, 4, "2026" )

SUBSTITUTE replaces matching text (all matches, or only the nth if you give an instance number) and is case sensitive. REPLACE replaces characters at a position: start, how many, new text. Use SUBSTITUTE when you know what to change and REPLACE when you know where.

Try it: the functions side by side

This stand-in applies the same operations in Python. The comments show the DAX each line imitates. Note the + 1 after find: Python returns a 0-based index and -1 when nothing is found, DAX returns a 1-based position.

Python Stand-in: slicing, searching and replacing

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The last two lines show a point that surprises people: in DAX, the = operator compares text without regard to case ("a" = "A" is TRUE), and only EXACT is case sensitive.

FORMAT: turn a value into text

FORMAT(value, format_string) converts a number, date or Boolean into a text string with the layout you ask for. It is how you build labels and titles.

DAX
Sales Label   = FORMAT ( [Total Sales], "#,0.00" )
Margin Label  = FORMAT ( [Margin %], "0.0%" )
Month Label   = FORMAT ( 'Date'[Date], "MMM yyyy" )
Weekday Name  = FORMAT ( 'Date'[Date], "dddd" )
Padded Number = FORMAT ( Orders[Number], "00000" )

For the date 5 March 2026 and the number 1234.5 these give 1,234.50, Mar 2026, Thursday and, for a number 42, 00042. Standard names such as "Currency", "Percent" and "Short Date" also exist, and they follow the regional settings of the report, so a date can appear differently for different viewers.

FORMAT always returns text. A text column sorts alphabetically, so a Month Label column built with FORMAT puts April and August before January. If you need it as an axis, either sort it by a numeric column (Column tools > Sort by column, see Model View, Hierarchies, Formatting and Display Folders) or format the number with the Format setting instead of FORMAT. Do not use FORMAT on a measure you plan to chart, because text cannot be plotted.

CONCATENATEX: many rows into one string

CONCATENATE joins two values, and & does the same. To join a whole column of values into one string you need CONCATENATEX, an iterator: it evaluates an expression for each row of a table, and joins the results with a delimiter.

DAX
Regions List =
CONCATENATEX (
    VALUES ( Customers[Region] ),
    Customers[Region],
    ", ",
    Customers[Region], ASC
)

Chart Title =
"Sales for " &
    IF ( HASONEVALUE ( Customers[Region] ), VALUES ( Customers[Region] ), "all regions" )

The arguments are the table, the expression, the delimiter, then an optional sort expression and direction. Without the sort, the order is not guaranteed. A very common use is a dynamic title: put a measure like Regions List in a card or in a visual's title (Format > Title > fx), and it changes with the slicers. For a title that tolerates several selections, CONCATENATEX is safer than VALUES alone, because VALUES returns an error when more than one value is selected in a scalar context.

Try it: building labels and lists

Python Stand-in: FORMAT, CONCATENATEX and the sorting trap

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

In the output, the text sort puts Apr 2026 and Aug 2026 before Jan 2026, which is exactly the problem the warning described.

Other useful text functions

  • COMBINEVALUES(delimiter, col1, col2, ...) builds a multi-column key, handy for a relationship on two columns.
  • UNICHAR(10) returns a line-feed character, so "Line 1" & UNICHAR(10) & "Line 2" breaks a line inside a title or tooltip.
  • VALUE("12.5") converts text to a number (see also Data Types and Conversions in Power BI).
  • FIXED(number, decimals, noCommas) returns a number as text with fixed decimals.
  • BLANK() returns an empty value, and ISBLANK tests for it. Blank is not an empty string: see DAX Logical and Information Functions.

Where to do text work

Text functions are cheap on a few thousand rows and expensive on millions. Row-by-row cleaning belongs in Power Query, where it is done once at refresh and can fold back to the source (Query Folding and Incremental Refresh). Use DAX text functions for labels that depend on the current selection, which only a measure can do.

Try it: cleaning a messy key

Python Stand-in: TRIM, UPPER and SUBSTITUTE as a cleaning pipeline

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Four spellings collapse into two products. That is the point of cleaning keys before they are used in a relationship or a slicer.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which function replaces characters at a given position, no matter what they are?

    REPLACE takes a start position and a count. SUBSTITUTE replaces matching text and does not care about positions.
  2. 2. What is true about FIND compared with SEARCH?

    FIND is exact, SEARCH is flexible. Both return a 1-based position and both need the notFound argument to avoid an error.
  3. 3. You build 'Month Label' with FORMAT(Date, "MMM yyyy") and the axis shows Apr before Jan. Why?

    Use Sort by column with a numeric month number, or keep the column as a date and use the Format setting.
  4. 4. Which function joins the values of many rows into one string with a delimiter?

    CONCATENATEX iterates a table and joins an expression with a delimiter. CONCATENATE only joins two values.
  5. 5. Does DAX have a PROPER function for Title Case?

    DAX has UPPER and LOWER only. Text.Proper in Power Query capitalizes each word.

Summary

  • LEN, LEFT, RIGHT and MID slice text with 1-based positions; TRIM also collapses inner spaces; DAX has no PROPER.
  • FIND is case sensitive, SEARCH is not and takes wildcards; give both a notFound value, or use CONTAINSSTRING for TRUE/FALSE.
  • SUBSTITUTE replaces by content, REPLACE by position.
  • FORMAT returns text, so it sorts alphabetically and cannot be plotted; use it for labels and titles only.
  • CONCATENATEX is an iterator that joins rows into one string; it is the tool for dynamic titles.
  • Clean text in Power Query; use DAX for labels that react to the selection.

Next: DAX Date and Time Functions.