TEXTJOIN is the go-to Excel function for joining text values with a delimiter. But it only works on cell ranges — it cannot iterate over a related table, respect PivotTable filter context, or sort values before joining them. CONCATENATEX is the DAX equivalent that removes all three limitations. It iterates over the rows of a table, evaluates an expression on each row, and joins the results into a single text string with a delimiter and optional sort order. Use it inside Power Pivot measures and calculated columns to produce product lists per order, employee rosters per department, tag strings per article, and any other grouped text aggregation.
CONCATENATEX Syntax and Parameters
CONCATENATEX accepts up to five parameters. The first two are required. The remaining three are optional but significantly expand what the function can produce — particularly the orderBy parameter, which controls sort order before joining and is the feature that most clearly differentiates CONCATENATEX from simple text iteration.
CONCATENATEX vs TEXTJOIN — Key Differences
Both functions join text values with a delimiter. However, they operate in completely different contexts. TEXTJOIN works on cell ranges in worksheet formulas. CONCATENATEX works on Data Model tables in DAX. The distinction determines which tool to use for which task — they are not interchangeable and one cannot substitute for the other.
Example 1: Product List per Order — Line Items as One Cell
An Orders table relates to an OrderItems table. CONCATENATEX iterates over all items for the current order and joins the product names into a comma-separated string. This is particularly useful for order confirmation reports and customer-facing summaries where all line items must appear in a single cell. RELATEDTABLE(OrderItems) automatically filters to only the items belonging to the current order in the PivotTable row context — no explicit filter is required.
Example 2: Employee Roster per Department — Alphabetical Names
An Employees table has a Department column. CONCATENATEX on a filtered Employees table creates a comma-separated roster of active employee names for each department. The sort parameter ensures names appear in consistent alphabetical order every time, regardless of the physical row order in the underlying table. This roster measure is specifically useful for HR reports, org chart supplements, and responsibility matrices where the members of each team must be listed explicitly.
Example 3: Conditional Concatenation — Only Values Above a Threshold
CONCATENATEX combined with FILTER produces conditional concatenation — only rows meeting a criterion are included in the output string. Items below the threshold are silently excluded. This pattern is useful for executive summaries that list only the products or regions that exceeded a performance target. The result updates automatically when slicers or PivotTable filters change, as CALCULATE re-evaluates the FILTER condition in the current filter context.
Example 4: Tags per Article — Many-to-Many via Bridge Table
Content systems use a many-to-many relationship between articles and tags via a bridge table. CONCATENATEX traverses this bridge table using RELATEDTABLE and RELATED to produce a pipe-separated tag list for each article in the PivotTable. This is specifically useful for content analytics where all tags for each article must appear in a single PivotTable cell. The measure handles any number of tags per article without any structural changes to the query.
Example 5: Revenue-Ordered Output — Products Sorted by Sales
The optional orderBy parameter sorts rows by the specified expression before joining them. This means the output string can list items by revenue (highest first), date (most recent first), or any other column. For executive summaries, listing products in revenue order communicates priority immediately without a separate sorting step. The expression argument can also include formatting — combining product name with formatted revenue in a single concatenated string.
Example 6: Calculated Column — Pre-Computed Static Concatenation
CONCATENATEX works in calculated columns as well as measures. In row context rather than filter context, it computes once per row at model refresh and stores the result statically. This is useful for adding a pre-computed summary string to each fact table row — for example, a comma-separated list of all items on an order stored directly in the Orders table. Calculated columns do not respond to slicer changes, however — use a measure when filter-context sensitivity is required.
Troubleshooting CONCATENATEX
Both issues below have specific causes and clear fixes. The first — blank output — is the most common problem and is almost always a relationship direction issue.
CONCATENATEX returns blank instead of the expected list
A blank result usually means the table argument is empty in the current filter context. Check that the relationship between tables is correctly defined and the filter direction flows from the "one" side to the "many" side. If the filter direction is reversed, RELATEDTABLE returns an empty table and CONCATENATEX produces blank. In Power Pivot Diagram View, the arrow on the relationship line shows the filter direction — it should point from the lookup table to the fact table. Also verify the measure is used in a PivotTable that correctly filters the source table to the expected rows.
The output string is truncated or the measure is very slow
CONCATENATEX can be slow when the table contains thousands of rows per group, and DAX text functions have a maximum output string length that can cause truncation for very large row counts. Use TOPN to limit the concatenation — for example, TOPN(10, Products, [Total Sales], DESC) restricts the concatenation to the 10 highest-revenue products, simultaneously solving both the performance issue and the truncation issue. The performance impact is most severe when the function is called in a calculated column on a large table.
The sort order in the output string is inconsistent or random
Without the optional orderBy parameter, CONCATENATEX joins values in the physical row order of the table — which is not guaranteed to be consistent and may change after a model refresh. Always specify the orderBy_expression and order arguments when the output string order matters. Specifically, use a column value (like [ProductName] for alphabetical or [Revenue] for value-based ordering) rather than relying on the default physical row order, which is an implementation detail and not a reliable sort basis.
Frequently Asked Questions
- What is CONCATENATEX in DAX and when should I use it?CONCATENATEX is a DAX iterator function that iterates over the rows of a table, evaluates an expression on each row, and joins all results into a single text string with a specified delimiter. Use it in Power Pivot measures and calculated columns when you need to combine text values from multiple related rows into a single cell — for example, a comma-separated product list per order, an employee roster per department, or a tag list per article. Unlike TEXTJOIN, CONCATENATEX respects PivotTable filter context and can traverse relationships to related tables using RELATED and RELATEDTABLE.
- Can CONCATENATEX filter which rows it includes in the output?Yes. Wrap the first parameter in FILTER: CONCATENATEX(FILTER(Products, [UnitPrice] > 100), [ProductName], ", "). Only rows where [UnitPrice] exceeds 100 are included in the concatenated output. You can also use FILTER with CALCULATE([measure]) as the condition to filter based on aggregated values — for example, only including products where total sales exceed a threshold. The FILTER expression is evaluated in the current PivotTable filter context, so the output changes automatically when slicers or filters change.
- How do I sort the values in a CONCATENATEX output string?Use the optional fourth and fifth parameters: orderBy_expression and order. For example, CONCATENATEX(Products, [ProductName], ", ", [ProductName], ASC) produces an alphabetically sorted list. CONCATENATEX(Products, [ProductName], ", ", [Total Sales], DESC) sorts by revenue descending, putting the highest-revenue product first in the string. Without the orderBy parameter, the output order follows the physical row order in the table — which is not guaranteed to be consistent across model refreshes.
- What is the difference between CONCATENATEX and TEXTJOIN in Excel?TEXTJOIN is a worksheet function that joins text from a cell range — it works in formulas on the Excel grid and has no awareness of the Power Pivot Data Model. CONCATENATEX is a DAX function that works only inside Power Pivot measures and calculated columns — it cannot be used in worksheet formulas. CONCATENATEX can traverse Data Model relationships using RELATED and RELATEDTABLE, respect PivotTable filter context, filter rows before joining using FILTER, and sort the output using orderBy — none of which TEXTJOIN supports.