DAX Formula to Concatenate Text in Power BI

In Power BI, use the DAX & operator to join text: Customers[FirstName] & " " & Customers[LastName]. Use CONCATENATE for two text values, or CONCATENATEX when you need to combine values from multiple rows into one text string.

Choose the right DAX concatenation method

These three options solve related but different problems. The & operator joins expressions directly and is usually the clearest choice for putting together several columns, words, or labels. CONCATENATE is a function that takes two arguments. CONCATENATEX iterates over a table, evaluates an expression for each row, and joins the results with an optional separator.

Use & to combine a few values, such as first and last names or a label and a measure.
Use CONCATENATE if you prefer a function for joining exactly two expressions.
Use CONCATENATEX to create a delimited list from values in several rows, such as products in the current selection.

For a basic calculated column, suppose the Customers table has FirstName and LastName columns. In Power BI Desktop, select the table, choose New column, and enter Full Name = Customers[FirstName] & " " & Customers[LastName]. The middle expression adds a space; without it, the result would run the two names together. The same expression can be adapted to other columns and separators.

How the ampersand operator works

The ampersand is DAX’s text-concatenation operator. It evaluates the expression on its left and right, converts values to text when needed, and joins the results. You can chain multiple parts in one expression. For instance, with Orders[OrderNumber] and Orders[Region] available in a calculated column, Order Label = "Order " & Orders[OrderNumber] & " - " & Orders[Region] could return Order 1048 - West.

Use quotation marks for text you want to insert literally, such as spaces, punctuation, or descriptive words. Column references are not quoted. If a literal string itself needs a quotation mark, DAX string-literal escaping needs care; for ordinary labels, it is simpler to avoid embedding quote marks unless they are part of the required output. Parentheses can make complex expressions easier to read, though a straightforward chain generally needs none.

Do not substitute the plus sign for the ampersand. In DAX, + is an addition operator, not the normal way to join text. It can produce errors or unintended numeric behavior rather than the label you meant. Use & when the goal is text, and use arithmetic operators when the goal is calculation.

Using CONCATENATE for two values

The CONCATENATE function takes two arguments and returns their combined text. For example, in a calculated column on Customers, Full Name = CONCATENATE(Customers[FirstName], " " & Customers[LastName]) joins the first name with a space and the last name. The second argument is itself an expression, which is why it can include both the separator and last name.

Because the function accepts only two arguments, joining three or more pieces requires nesting calls or building an expression inside an argument. For example, you could nest CONCATENATE, but a chain with & is often easier to scan and maintain. Both approaches can return the same text; choose based on clarity rather than expecting the function to provide a special formatting or performance advantage.

When a value is a number or date, make the intended display explicit if the exact text matters. DAX can convert values during concatenation, but an implicit conversion may not use the number of decimal places, date pattern, or currency presentation you want. The FORMAT function turns a value into text using a format string. For example, "Sales: " & FORMAT([Total Sales], "$#,##0.00") creates a label with two decimal places, assuming [Total Sales] is a measure. Formatting makes the result text, so do not use that formatted result as a replacement for the numeric measure in calculations.

Combining values from multiple rows with CONCATENATEX

CONCATENATEX is the relevant function when the source values occupy multiple rows. Its basic pattern is CONCATENATEX(table, expression, delimiter). The table argument supplies the rows to visit, the expression supplies the text to produce for each row, and the delimiter is inserted between results. Optional arguments let you specify a sort expression and sort direction.

For example, a measure can list distinct product names visible in the current filter context: Product List = CONCATENATEX(VALUES(Products[ProductName]), Products[ProductName], ", ", Products[ProductName], ASC). If the current report selection contains “Notebook,” “Pen,” and “Folder,” the result is Folder, Notebook, Pen. VALUES returns the distinct product names in the relevant context, while the last arguments request ascending order by product name.

If repeated values should remain in the output, use a table expression that includes the underlying rows rather than VALUES. For example, iterating over an orders table can list a product once per matching order. Think about duplicates before choosing the table expression: a distinct list answers a different question from a list of every transaction. Also consider the context in which a measure is evaluated. In a table visual grouped by customer, the measure will generally produce a list for each customer’s filter context, provided the model relationships allow that customer selection to filter the relevant rows.

Calculated columns, measures, and filter context

A calculated column is evaluated for each row when the model is refreshed. It is useful for a fixed row-level label, such as a full name or a combined code, and the resulting values are stored in the model. A measure is evaluated when a visual requests it, using the current filter context. That makes measures useful for dynamic captions or lists that change with slicers and visual selections.

For example, a measure defined as Sales Caption = "Sales: " & FORMAT([Total Sales], "$#,##0") can display a formatted total in a card and respond to the report’s filters. By contrast, putting an aggregate measure into a calculated column does not make that column respond to slicers. The distinction matters: choose a column for a value that belongs to each model row, and a measure for a result that should recalculate under report context.

Concatenation does not create a relationship or combine tables in the data model. It only creates a text result from the expressions or rows you provide. If the goal is to match records across tables, use model relationships or an appropriate lookup approach; joining two displayed values into a string does not establish a reliable key or make unmatched records behave like related data.

Handling blanks, spaces, and display formats

Blank inputs can make labels look untidy. A basic name expression may leave an extra space when one name part is blank. One way to normalize the result is to replace blank inputs with empty strings and trim the combined text. In a calculated column on Customers, use Full Name = VAR GivenName = COALESCE(Customers[FirstName], "") VAR FamilyName = COALESCE(Customers[LastName], "") RETURN TRIM(GivenName & " " & FamilyName). COALESCE returns the first nonblank argument, and here supplies an empty string when a name is blank; TRIM removes leading and trailing spaces and reduces repeated spaces between words.

That pattern suits names, but a blank may have meaning in other data. Replacing a missing region with an empty string, for example, can hide incomplete records. If readers need to know that a value is absent, show a clear fallback such as “Unknown” with an IF or COALESCE expression instead. Decide whether a blank should disappear, remain blank, or become an explanatory label before building the concatenation.

Use a deliberate delimiter that makes the parts readable: a space for names, a comma and space for a short list, or a hyphen for a code. In row-concatenation measures, the delimiter is applied between returned rows, not automatically before the first or after the last item. For numbers and dates, use FORMAT when a specific display is required, and remember that formatting strings can be affected by locale and the model’s regional settings.

Common problems and practical limits

The output has no separator: Add a literal such as " " or ", " between the expressions.
A date or number looks unexpected: Convert it with FORMAT and specify the intended pattern, rather than relying on implicit conversion.
A CONCATENATE formula has too many arguments: It accepts two arguments. Use & for several pieces or nest function calls.
A list repeats names: Check whether the table expression includes duplicate rows. Use VALUES when a distinct list is appropriate.
A list appears in an unexpected order: Supply an ordering expression and direction to CONCATENATEX; do not assume the source table’s order will be preserved.
A measure does not change as expected: Check its filter context, the fields in the visual, and the relationships between the tables used by the measure.

Long strings also have a practical usability cost. A measure that concatenates many transactions may become difficult to read in a card or table and can create a large result. Consider whether a count, a filtered table visual, or a drill-through page would communicate the information better. Concatenated text is best for compact labels and manageable lists, not as a substitute for a detailed data view.

When to use Power Query or another approach

If two source columns need to be combined once during data preparation and the result should not respond to report filters, Power Query may be a better place to create the column. In Power BI Desktop, open Transform data to launch Power Query Editor, select the relevant columns, and use Merge Columns from the Transform tab. Choose a separator and confirm the new column. Power Query transformations are applied during refresh, whereas a DAX measure can respond dynamically to filter context.

For a DAX expression, use & for a straightforward row-level combination, and use CONCATENATEX for a context-sensitive list across rows. Power Query is often appropriate for shaping imported data; DAX is appropriate when the result belongs in the model’s calculation layer or needs to vary with report context. Neither is universally superior. Consider when the value should be computed, whether it needs to change with filters, and whether it is intended for display or further analysis.

Frequently Asked Questions

What is the DAX formula to combine first and last names?
In a calculated column on a table with FirstName and LastName, use Full Name = Customers[FirstName] & " " & Customers[LastName]. Replace the table and column names with those in your model.

Can I concatenate more than two columns in DAX?
Yes. Chain expressions with &, inserting literal separators where needed. CONCATENATE takes two arguments per call, so chaining is usually simpler when combining several columns.

How do I concatenate rows into a comma-separated list?
Use CONCATENATEX with a table expression, the text expression to return for each row, and ", " as the delimiter. Use VALUES if you want distinct values, and add sort arguments if you need a defined order.

Does CONCATENATEX remove duplicates?
No. It concatenates the results for the rows in the table expression you pass to it. Use a distinct table expression such as VALUES(SomeTable[SomeColumn]) when the intended result is a list of unique values.

Should I use a calculated column or a measure?
Use a calculated column for a row-level result that is set at refresh time. Use a measure when the text should respond to report filters, such as a formatted total or a list of items in the current selection.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.