In Power BI DAX, join two text values with the ampersand operator (&) or the CONCATENATE function; to combine values from multiple rows into one string, use CONCATENATEX. For example, 'Customer'[First Name] & " " & 'Customer'[Last Name] builds a full name, while CONCATENATEX can list the categories in the current report context.
What concatenation means in DAX
Concatenation means joining text end to end. It does not combine rows, create a relationship, or change the underlying values in two columns. It produces a text result from values that DAX evaluates in a particular context. The result might be stored in a calculated column or calculated dynamically by a measure.
DAX offers three practical ways to do this:
&joins expressions and is usually the clearest choice for two or more pieces of text.CONCATENATE(text1, text2)joins exactly two text expressions.CONCATENATEX(table, expression, delimiter, ...)evaluates an expression for each row in a table and joins the resulting values.
The key distinction is the number of values and rows involved. Use & or CONCATENATE to join fields on the current row. Use CONCATENATEX when the values you want to join come from a set of rows, such as the products visible under a slicer selection.
Join columns with the ampersand operator
For a calculated column that combines first and last names, select Modeling > New column and enter this expression, assuming the table is named Customer and contains the two named columns:
Full Name = 'Customer'[First Name] & " " & 'Customer'[Last Name]
The space inside the quotation marks becomes the separator. If the row contains “Ava” and “Chen,” the result is “Ava Chen.” You can join more pieces by adding another ampersand, such as a middle initial or a suffix. Text literals must be enclosed in straight double quotation marks.
If names might be missing, a simple separator can leave an unwanted space at the beginning or end. A common cleanup is to wrap the expression in TRIM, which removes leading and trailing ordinary spaces and reduces repeated ordinary spaces:
Full Name = TRIM('Customer'[First Name] & " " & 'Customer'[Last Name])
Check the result with realistic blanks before relying on it. TRIM does not remove every kind of whitespace, such as all nonbreaking spaces copied from external text. If the source data contains unusual characters, it may need cleaning at the source or in Power Query.
When to use CONCATENATE
CONCATENATE is the named DAX function for joining two text expressions. With first and last name columns in the Customer table, a calculated column could be:
Full Name = CONCATENATE('Customer'[First Name], 'Customer'[Last Name])
This returns the names joined directly, with no separator. To include a space, the second argument can combine the space and last name with &: CONCATENATE('Customer'[First Name], " " & 'Customer'[Last Name]). In everyday DAX, using & throughout is often easier to read, especially when joining three or more parts.
Despite its name, CONCATENATE is not the function for combining a column across many rows. It accepts two text expressions, not a table of values. For a list of values from multiple records, choose CONCATENATEX.
Combine values from multiple rows with CONCATENATEX
CONCATENATEX iterates over a table, evaluates an expression for each row, and joins the results. For example, a measure can list distinct product categories visible in the current report context. Assuming a table and column named Product and Category, create a measure with Modeling > New measure:
Visible Categories = CONCATENATEX(VALUES('Product'[Category]), 'Product'[Category], ", ", 'Product'[Category], ASC)
VALUES supplies the distinct category values visible under the current filters. The second argument is the expression to return for each value; ", " is the separator; and the final arguments sort the output by category in ascending order. If the report context contains “Accessories,” “Bikes,” and “Clothing,” the measure returns Accessories, Bikes, Clothing.
The measure updates as report filters and slicers change. A calculated column, by contrast, is evaluated for each row when the model is refreshed and does not recalculate merely because a user clicks a slicer. That difference determines which kind of DAX expression is appropriate.
Choose a measure or calculated column
Use a calculated column when the joined text is an attribute of each individual row and needs to be used in a slicer, axis, grouping, or relationship design. A full name, product label, or combined city-and-region display field is a typical example. The value is stored in the model, so adding large text columns can increase model size.
Use a measure when the output should respond to filters or summarize multiple records into one result. A comma-separated list of selected categories is a measure-style task. Measures are evaluated when a visual requests a value, in the filter context for that visual; they are not stored as a separate value for every source row.
Do not concatenate columns just to make a new key unless there is a clear reason. Combining two identifiers can create collisions: for example, values AB and C produce the same text as A and BC if no safe separator is used. A display label is not automatically a reliable unique key. For modeling and relationships, prefer stable source keys or a carefully designed key that cannot be ambiguous.
Handle blanks, numbers, and dates
DAX can treat a blank text value as an empty piece during concatenation, but the separator you add is still part of the expression. That is why a missing last name can leave a trailing space in a full-name result. Decide what a missing value should mean: omit the separator, show a placeholder, or return a blank result. For multirow lists, you can filter unwanted blank values out of the table passed to CONCATENATEX.
Numbers and dates need deliberate formatting when you join them to text. A date or numeric value may be converted to text, but relying on implicit conversion can make the displayed result unclear or dependent on formatting settings. Use FORMAT when you need a chosen display format. For example, with an order-date column in the Orders table, a calculated column expression could be FORMAT('Orders'[Order Date], "yyyy-MM-dd"). To combine it with a label, use "Order date: " & FORMAT('Orders'[Order Date], "yyyy-MM-dd").
Remember that FORMAT returns text. Once a number or date has been formatted as text, it is no longer a numeric or date result for sorting, arithmetic, or date hierarchy behavior. Keep the original typed column for calculations and use the formatted string only where a text display is required.
Control separators, order, and line breaks
The delimiter in CONCATENATEX is inserted between results, not after every result. Use a comma and space for a readable list, a semicolon when that suits the audience, or another separator that will not be confused with the values themselves. If the values can contain the delimiter, consider whether the output will still be understandable or easy to parse.
Do not assume a table has a meaningful display order. Supply an order-by expression when the order matters, as in the category example. You can sort by a different column if that is useful—for instance, concatenate month names using a month-number column as the ordering expression. The ordering column should represent the intended order, rather than relying on alphabetical sorting.
For a line-separated list, use UNICHAR(10) as the delimiter in a CONCATENATEX measure. Whether line breaks appear as intended depends on the visual and its text-wrapping behavior; a table, card, or other visual may display the result differently. Test the chosen visual rather than assuming that a newline will create a new row or expand the visual automatically.
Common problems and practical checks
- Using the wrong function: If DAX reports that an argument is missing or the expression is invalid, check whether you need a two-value join or a table iterator.
CONCATENATEtakes two expressions;CONCATENATEXtakes a table and an expression. - Unexpected order: Add an explicit order-by expression and ascending or descending direction when list order matters.
- Repeated results:
VALUESreturns distinct values in its filter context. If you instead pass a table containing repeated rows toCONCATENATEX, repeated text can appear in the result. Choose or filter the input table to match the intended list. - Unexpected blank item: A model relationship can expose a blank member when related records do not match. If it should not appear, filter blank values from the input table and investigate the relationship or source data.
- Wrong-looking dates or numbers: Format the value explicitly when converting it to text, and retain the original typed field for numeric or date operations.
- Measure used where a column is needed: Measures are evaluated in visual context and cannot serve as a per-row stored label in the same way as calculated columns. Choose the object based on how the result will be used.
A useful debugging approach is to build the expression in small pieces. First confirm the source columns and the row or filter context. Then add the separator, formatting, and ordering. For CONCATENATEX, inspect the table expression independently: if that table contains unexpected duplicates or blanks, changing the text expression will not fix the underlying list.
How DAX compares with Power Query
DAX and Power Query can both produce combined text, but they work at different stages. Power Query transforms data as it is loaded or refreshed, before it enters the model. DAX calculates values within the data model, either as stored calculated columns or as measures evaluated in report context.
For a row-level label that should be part of the transformed source data, Power Query may be a natural place to combine columns. For a result that changes with report selections, use a DAX measure. Neither choice is universally faster or better: consider when the result must update, whether it is row-level or context-dependent, and how the model will use it. A concatenated label also does not replace proper data modeling or a numeric measure when the desired output is a calculation.
Frequently Asked Questions
What is the DAX formula to concatenate two columns?
Use the ampersand operator, for example 'Customer'[First Name] & " " & 'Customer'[Last Name]. It joins the values with one space between them.
What is the difference between CONCATENATE and CONCATENATEX?CONCATENATE joins two text expressions. CONCATENATEX evaluates an expression over a table and joins the row results, optionally using a delimiter and sort order.
Can I concatenate values selected in a slicer?
Yes. A measure using CONCATENATEX over a table such as VALUES('Product'[Category]) responds to the filter context, including applicable slicer selections.
How do I add a comma between concatenated values?
Pass ", " as the delimiter argument in CONCATENATEX. The delimiter appears between results, not at the end of the list.
Why does my concatenated date look different from expected?
Use FORMAT with an explicit format string when you need a particular text representation. Keep the original date column for date sorting, filtering, and calculations because FORMAT returns text.
Related
- Scatter Plot Behavior Data Sheet: A Practical Guide
- Hide Column Headers in Power BI Table Visuals
- Power Query String Length: How to Count Characters
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for an Entire Column: How to Use ItTo calculate values from an entire column, use a whole-column reference such as A:A inside a function. To…
- XLOOKUP Match Mode: Exact, Approximate, and WildcardsIn Excel, XLOOKUP’s match_mode is its fifth argument: use 0 for an exact match, -1 for an exact…
- Text Join in Excel: Formulas and Practical ExamplesIn Excel for Microsoft 365, use TEXTJOIN to combine text from cells with a separator such as a…
- Age Formula in Excel Without DATEDIFTo calculate age in completed years in Excel without DATEDIF, put a birth date in A2 and enter…
- Concat Excel Cells with Dates: Formulas, Fixes, and TipsTo concatenate text and a date in Excel, use the TEXT function to format the date before joining…
- Excel Sum Lookup: Sum Values That Match a CriterionTo add every value that matches a lookup criterion in Excel, use SUMIF. With product names in A2:A10,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.