Power Pivot Principles: The A to Z of DAX Functions – CONCATENATEX

In our long-established Power Pivot Principles articles, we continue our series on the A to Z of Data Analysis eXpression (DAX) functions. This week, we look at CONCATENATEX.

The CONCATENATEX function

This function joins the result of an expression evaluated for each row in a table.  The CONCATENATEX function employs the following syntax to operate:

Returning a text string, the CONCATENATEX function has the following arguments:

• table: this is required and represents the table containing the rows for which the expression will be evaluated
• expression: this is also required and represents the expression to be evaluated for each row of the table
• delimiter: this is optional, and is a separator to use during concatenation
• orderBy_expression: again optional, this is any DAX expression where the result value is used to sort the concatenated values in the output string.  It is evaluated for each row (record) of the table
• order: also optional, this argument represents the value that specifies how to sort orderBy_expression values, ascending or descending.

The order parameter accepts the following values:

It should be further noted that:

• this function takes as its first argument a table or an expression that returns a table. The second argument is a column that contains the values you want to concatenate, or an expression that returns a value
• concatenated values are not necessarily sorted in any particular order, unless orderBy_expression is specified
• this function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Please see my example below:

Using the table Employees:

the formula

will return

“Kathryn Newitt, Liam Bastick, Tim Heng”.

Come back next week for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.