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.
The default order is ASC (ascending) when OrderBy_Expression argument is present and the corresponding Order argument is not specified.
» 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
-
Reading active Power BI security roles in DAX
This article describes how to read the active security roles in a Tabular model for Power BI or Analysis Services. This way, you can use measures and calculation groups to customize a report based dynamically on security roles active for the current user. » Read more
Related functions
Other related functions are:
Last update: Nov 14, 2024 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo, Francis Romio, Airat Gabdrakhmanov
Microsoft documentation: https://docs.microsoft.com/en-us/dax/concatenatex-function-dax