CONCATENATEX DAX Function (Text)
Evaluates expression for each row on the table, then return the concatenation of those values in a single string result, seperated by the specified delimiter.
Syntax
| Parameter | Attributes | Description |
|---|---|---|
|
Table Iterator |
The table containing the rows for which the expression will be evaluated. |
|
|
Expression Row Context |
The expression to be evaluated for each row of the table. |
|
| Delimiter | Optional |
The delimiter to be concatenated with expression. |
|
OrderBy_Expression Row Context |
Optional Repeatable |
Expression to be used for sorting the table. |
| Order | Optional Repeatable |
The order to be applied. 0/FALSE/DESC – descending; 1/TRUE/ASC – ascending. |
Return values
A text string with the concatenated values.
Remarks
This function iterates the rows in the table provided by the first argument and for each row it executes the expression provided in the second argument. All the expressions are concatenated using the separator provided as third argument.
» 1 related function
Examples
-- CONCATENATEX is an iterator that produces the concatenation
-- of expressions evaluated during the iteration.
-- You provide the expression, a separator and optional
-- sorting expressions.
EVALUATE
ADDCOLUMNS (
VALUES ( 'Product'[Category] ),
"Category colors",
CONCATENATEX (
CALCULATETABLE ( VALUES ( 'Product'[Color] ) ),
Product[Color],
", ", -- Separator (optional)
Product[Color], -- Sorting expression (optional)
ASC -- Sorting direction (optional)
)
)
| Category | Category colors |
|---|---|
| Audio | Black, Blue, Green, Orange, Pink, Purple, Red, Silver, White, Yellow |
| TV and Video | Black, Brown, Silver, White |
| Computers | Black, Blue, Brown, Gold, Green, Grey, Orange, Pink, Red, Silver, White, Yellow |
| Cameras and camcorders | Azure, Black, Blue, Gold, Green, Grey, Orange, Pink, Purple, Red, Silver, Silver Grey, White, Yellow |
| Cell phones | Black, Gold, Grey, Pink, Red, Silver, Transparent, White |
| Music, Movies and Audio Books | Black, Blue, Gold, Grey, Red, Silver, White, Yellow |
| Games and Toys | Black, Blue, Gold, Green, Grey, Pink, Purple, Red, Silver, White, Yellow |
| Home Appliances | Black, Blue, Brown, Gold, Green, Grey, Orange, Pink, Purple, Red, Silver, White, Yellow |
Related articles
Learn more about CONCATENATEX in the following articles:
-
Displaying Nth Element in DAX
This article describes how to create a measure displaying the name or value of an element that has a specific ranking, with different option for managing ties. » Read more
-
Displaying filter context in Power BI Tooltips
This article describes how to display the filter context applied to a calculation using a special DAX measure in Power BI Tooltips. » Read more
-
Table and column references using DAX variables
This article describes how to correctly use column references when manipulating tables assigned to DAX variables, avoiding syntax errors and making the code easier to read and maintain. » Read more
-
Using CONCATENATEX in measures
This article showcases the use of CONCATENATEX, a handy DAX function to return a list of values in a measure. » Read more
Related functions
Other related functions are:
Last update: May 12, 2021 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo
Microsoft documentation: https://docs.microsoft.com/en-us/dax/concatenatex-function-dax