CONCATENATEX
Evaluates an expression on each row of a table and joins the results into one text value.
CONCATENATEX(<table>, <expression> [, <delimiter>] [, <orderBy_expression> [, <order>]])Returns: Text (a scalar).
What it does
CONCATENATEX walks a table, takes a value from each row, and joins them into a single text string with a delimiter between them. It answers "which products did this customer buy" or "which stores are in this region" in one line. The optional order expression lets you control the order of the list, such as months in calendar order.
Syntax and arguments
- table: the rows to list, such as
VALUES(Product[ProductCategory])or a filtered table. - expression: the text to join for each row.
- delimiter (optional): the text placed between items, such as ", ".
- orderBy_expression (optional): a value that sets the order of the items. It is not shown.
- order (optional): ASC or DESC, to control the direction of the order.
Example
The regional manager wants a line on the dashboard that lists the product categories sold in the selected store group.
Categories Sold =
CONCATENATEX(
VALUES(Product[ProductCategory]),
Product[ProductCategory],
", "
)
VALUES gives one row per category in context. The categories are joined with a comma and a space, such as "Audio, Cameras, Computers". Long lists can run past the width of a card, so keep the number of categories small or use a table.
Example: a second, different use
A list of months in calendar order, ordered by the month number rather than alphabetically:
Months In Period =
CONCATENATEX(
VALUES('Date'[MonthName]),
'Date'[MonthName],
", ",
'Date'[MonthNumber],
ASC
)
Without the order expression, the names would sort alphabetically, which puts April before January. The month number controls the order and is not shown in the text.
Things to watch
- The result can be very long. Check the length for big lists, and limit the table with a filter first.
- Measures can be used as the expression, which is evaluated for each row. This can be slow on large tables.
- Order is not guaranteed unless you give an order expression. Set one whenever the sequence matters.
- Blank values can make a list look broken. Filter them out before joining.
- The result is text. It cannot be summed or sorted as a number.
In business terms
CONCATENATEX builds the text of a management commentary: which entities are in scope, which accounts were adjusted, which vendors were paid in a run, which clients are on a retainer. It turns a table into the sentence a reader needs.