The Complete Guide to Text Concatenation in Power BI

How do you concatenate text in Power BI? Use the ampersand (&) operator to join text values: FullName = [FirstName] & " " & [LastName]. For aggregating text across table rows, use CONCATENATEX. For composite keys in DirectQuery, use COMBINEVALUES. The legacy CONCATENATE function works but only accepts two arguments — the & operator is preferred by Microsoft for readability and flexibility.

Text concatenation is one of the most frequently needed operations in Power BI development. Whether you are building dynamic report titles, creating composite keys for relationships, formatting labels for visuals, or generating summary strings from table data, you need to understand which DAX function to use and when.

This guide covers every DAX text concatenation method with production-ready code examples. We move from the basics — CONCATENATE and the & operator — through advanced patterns like CONCATENATEX for row-level aggregation, COMBINEVALUES for DirectQuery optimization, and FORMAT for number-to-text conversion.

EPC Group's Power BI consulting team builds enterprise DAX solutions for Fortune 500 organizations. The patterns in this guide come directly from our production implementations.

CONCATENATE Function Basics

The CONCATENATE function joins exactly two text strings into one. It is the original DAX text-joining function, inherited from the Excel function of the same name.

// CONCATENATE syntax — exactly two arguments

CONCATENATE(<text1>, <text2>)

// Basic example: join first and last name
Full Name = CONCATENATE(Customer[FirstName], Customer[LastName])
// Result: "JohnSmith" — no space between values!

// With a space separator
Full Name = CONCATENATE(Customer[FirstName], CONCATENATE(" ", Customer[LastName]))
// Result: "John Smith"

// Three values require nesting
Full Name = CONCATENATE(
    CONCATENATE(Customer[FirstName], " "),
    Customer[LastName]
)

// Four values — nesting becomes painful
Full Address Line = CONCATENATE(
    CONCATENATE(
        CONCATENATE(Customer[Street], ", "),
        CONCATENATE(Customer[City], ", ")
    ),
    CONCATENATE(Customer[State], " " & Customer[Zip])
)

Key limitation: CONCATENATE only accepts two arguments. For three or more values, nesting is required, which quickly becomes unreadable. This is why Microsoft recommends the & operator instead.

The & Operator vs CONCATENATE

The ampersand (&) operator is the recommended way to concatenate in Power BI. It accepts unlimited operands, reads left to right, and performs identically to CONCATENATE under the hood.

// The & operator — unlimited arguments, clean syntax

// Simple two-value join
Full Name = Customer[FirstName] & " " & Customer[LastName]
// Result: "John Smith"

// Multiple values — no nesting needed
Full Address =
    Customer[Street] & ", " &
    Customer[City] & ", " &
    Customer[State] & " " &
    Customer[Zip]
// Result: "123 Main St, Houston, TX 77001"

// Mix text and numbers — automatic type conversion
Order Summary =
    "Order #" & Sales[OrderID] & " — " &
    Sales[Quantity] & " units @ $" & Sales[UnitPrice]
// Result: "Order #1042 — 5 units @ $29.99"

// With line breaks for multi-line card visuals
KPI Card Text =
    "Revenue: " & FORMAT(SUM(Sales[Amount]), "$#,##0") &
    UNICHAR(10) &
    "Orders: " & COUNTROWS(Sales) &
    UNICHAR(10) &
    "Avg: " & FORMAT(AVERAGE(Sales[Amount]), "$#,##0.00")

CONCATENATE (Avoid)

Only 2 arguments per call
Requires nesting for 3+ values
Hard to read and maintain
Legacy Excel compatibility only

& Operator (Recommended)

Unlimited operands
Clean left-to-right readability
Identical performance to CONCATENATE
Microsoft-recommended approach

CONCATENATEX for Table Aggregation

CONCATENATEX is the only DAX function that aggregates text across multiple rows. It iterates over a table, evaluates an expression for each row, and joins the results with a delimiter. This is the function you need for comma-separated lists, dynamic bullet points, and row-level text summaries.

// CONCATENATEX — aggregate text from table rows

// Syntax
CONCATENATEX(<table>, <expression>, [delimiter], [orderBy], [order])

// List all product names, comma-separated
Product List =
    CONCATENATEX(
        Products,
        Products[ProductName],
        ", ",
        Products[ProductName], ASC
    )
// Result: "Gadget, Tool, Widget"

// List products purchased by the selected customer
Customer Products =
    CONCATENATEX(
        VALUES(Sales[ProductName]),
        Sales[ProductName],
        ", ",
        Sales[ProductName], ASC
    )
// Result: "Laptop, Monitor, Mouse" (filtered by slicer context)

// Build a summary with calculated values per row
Sales Summary =
    CONCATENATEX(
        TOPN(5, Products, [Total Revenue], DESC),
        Products[ProductName] & ": " &
            FORMAT([Total Revenue], "$#,##0"),
        " | ",
        [Total Revenue], DESC
    )
// Result: "Widget: $125,000 | Gadget: $98,000 | Tool: $74,500..."

// Multi-line list for card visuals
Top Categories =
    "Top Categories:" & UNICHAR(10) &
    CONCATENATEX(
        TOPN(3, VALUES(Product[Category]), [Total Sales]),
        "• " & Product[Category] &
            " (" & FORMAT([Total Sales], "$#,##0") & ")",
        UNICHAR(10),
        [Total Sales], DESC
    )

Performance Note

CONCATENATEX is an iterator function — it evaluates the expression for every row. On tables with millions of rows, this can be slow. Always filter the table to only the rows you need before passing it to CONCATENATEX. Use VALUES(), TOPN(), or FILTER() to reduce the row count.

COMBINEVALUES for Relationship Optimization

COMBINEVALUES looks like a simple concatenation function, but it has a critical optimization purpose: when used to create composite key columns, it enables DirectQuery to push joins to the source database as separate column comparisons rather than string matching.

// COMBINEVALUES — optimized composite keys

// Syntax
COMBINEVALUES(<delimiter>, <expression1>, <expression2>, ...)

// Create a composite key for a relationship
// In the Sales table (calculated column):
SalesKey = COMBINEVALUES("_", Sales[StoreID], Sales[ProductID], Sales[Date])

// In the Budget table (calculated column):
BudgetKey = COMBINEVALUES("_", Budget[StoreID], Budget[ProductID], Budget[Date])

// Now create a relationship: Sales[SalesKey] → Budget[BudgetKey]

// WHY this matters for DirectQuery:
// With & operator: Power BI sends
//   WHERE CONCAT(StoreID, '_', ProductID, '_', Date) = '42_101_2026-01-15'
//   → Cannot use indexes, full table scan
//
// With COMBINEVALUES: Power BI sends
//   WHERE StoreID = 42 AND ProductID = 101 AND Date = '2026-01-15'
//   → Uses indexes on each column, dramatically faster

// Multiple column composite key example
CompositeKey = COMBINEVALUES(
    "|",
    Inventory[WarehouseCode],
    Inventory[ProductSKU],
    Inventory[BatchNumber]
)

Rule of thumb: Use COMBINEVALUES for composite relationship keys. Use & for everything else. If your model uses DirectQuery or dual storage mode, this distinction directly impacts query performance.

FORMAT Function with Concatenation

The FORMAT function converts numbers, dates, and times to text strings with a specified format pattern. When combined with the & operator, it enables you to build human-readable labels with properly formatted values.

// FORMAT + concatenation patterns

// Currency formatting
Revenue Label = "Total Revenue: " & FORMAT(SUM(Sales[Amount]), "$#,##0.00")
// Result: "Total Revenue: $1,234,567.89"

// Percentage formatting
Growth Label = "YoY Growth: " & FORMAT([YoY Growth], "+0.0%;-0.0%;0.0%")
// Result: "YoY Growth: +12.5%" or "YoY Growth: -3.2%"

// Date formatting
Report Date = "Report as of " & FORMAT(TODAY(), "MMMM D, YYYY")
// Result: "Report as of April 5, 2026"

// Compact number formatting (K, M, B)
Compact Revenue =
    VAR Rev = SUM(Sales[Amount])
    RETURN
        "Revenue: " &
        IF(Rev >= 1E9, FORMAT(Rev / 1E9, "#,##0.0") & "B",
        IF(Rev >= 1E6, FORMAT(Rev / 1E6, "#,##0.0") & "M",
        IF(Rev >= 1E3, FORMAT(Rev / 1E3, "#,##0.0") & "K",
        FORMAT(Rev, "#,##0"))))
// Result: "Revenue: 1.2B" or "Revenue: 450.3K"

// Combining multiple formatted values
KPI Summary =
    "Revenue: " & FORMAT([Total Revenue], "$#,##0") &
    " | Margin: " & FORMAT([Profit Margin], "0.0%") &
    " | Orders: " & FORMAT([Order Count], "#,##0")

Performance warning: FORMAT is one of the most expensive DAX functions. It prevents query folding and is slow inside iterators like CONCATENATEX. Always apply FORMAT as the last step — aggregate first, format the final result. Never use FORMAT inside CONCATENATEX on large tables.

Dynamic Titles and Labels

Dynamic titles that respond to slicer selections make reports feel interactive and professional. The pattern combines SELECTEDVALUE, FORMAT, and the & operator to build context-aware strings.

// Dynamic titles — respond to slicer context

// Chart title that reflects slicer selection
Chart Title =
    "Sales Performance — " &
    SELECTEDVALUE(Date[Year], "All Years") & " | " &
    SELECTEDVALUE(Region[Name], "All Regions")
// Result: "Sales Performance — 2026 | Northeast"

// Subtitle with KPI summary
Chart Subtitle =
    FORMAT(SUM(Sales[Revenue]), "$#,##0") & " total across " &
    COUNTROWS(VALUES(Product[Category])) & " categories"
// Result: "$4,500,000 total across 12 categories"

// Conditional dynamic title
Trend Title =
    VAR CurrentRev = [Current Period Revenue]
    VAR PriorRev = [Prior Period Revenue]
    VAR Change = DIVIDE(CurrentRev - PriorRev, PriorRev)
    RETURN
        IF(Change > 0, "📈 ", "📉 ") &
        "Revenue " &
        IF(Change > 0, "Up ", "Down ") &
        FORMAT(ABS(Change), "0.0%") &
        " vs Prior Period"
// Result: "📈 Revenue Up 8.3% vs Prior Period"

// Page-level summary for report header
Report Header =
    "Showing " &
    FORMAT(COUNTROWS(Sales), "#,##0") & " transactions | " &
    FORMAT(MIN(Date[Date]), "MMM D") & " – " &
    FORMAT(MAX(Date[Date]), "MMM D, YYYY")

To connect these measures to visual titles in Power BI Desktop, select the visual, go to the Format pane, expand the Title section, click the fx (conditional formatting) button next to the Title Text field, and select your measure. The title updates automatically whenever the filter context changes.

Common Patterns: Address Builder & More

These production-ready patterns solve the most common text concatenation scenarios in enterprise Power BI models.

// Multi-column address builder with BLANK handling

// Full address with optional fields (handles BLANKs)
Full Address =
    VAR Street = Customer[Street]
    VAR Suite = Customer[Suite]
    VAR City = Customer[City]
    VAR State = Customer[State]
    VAR Zip = Customer[Zip]
    VAR Line1 =
        Street &
        IF(NOT ISBLANK(Suite), ", Suite " & Suite, "")
    VAR Line2 = City & ", " & State & " " & Zip
    RETURN
        Line1 & UNICHAR(10) & Line2
// Result: "123 Main St, Suite 400
//          Houston, TX 77001"

// Full name with optional middle initial
Display Name =
    VAR First = Customer[FirstName]
    VAR Middle = Customer[MiddleName]
    VAR Last = Customer[LastName]
    RETURN
        First &
        IF(
            NOT ISBLANK(Middle),
            " " & LEFT(Middle, 1) & ". ",
            " "
        ) &
        Last
// Result: "John A. Smith" or "Jane Doe"

// Email-style display: "Name <email>"
Contact Display =
    Customer[FullName] & " <" & Customer[Email] & ">"

// SKU generator from multiple fields
Product SKU =
    UPPER(LEFT(Product[Category], 3)) & "-" &
    FORMAT(Product[ProductID], "0000") & "-" &
    UPPER(LEFT(Product[Color], 2))
// Result: "ELE-0042-BL" (Electronics, ID 42, Blue)

Performance Tips for Text Functions

Text operations in DAX are inherently more expensive than numeric calculations. These guidelines keep your concatenation logic fast even at enterprise scale. For comprehensive performance tuning, see our Power BI Performance Optimization Guide.

Use VAR to avoid recalculation

Assign repeated expressions to variables. The DAX engine evaluates each VAR once and reuses the result. This matters when the same column reference appears in multiple concatenation branches.

Filter before CONCATENATEX

Always reduce the table size before passing it to CONCATENATEX. Use VALUES(), TOPN(), or FILTER() to limit rows. Concatenating 1 million rows is expensive — concatenating the top 10 is instant.

FORMAT last, not first

FORMAT prevents query folding and is slow inside iterators. Aggregate your numbers first, then apply FORMAT to the final result. Never place FORMAT inside CONCATENATEX on large tables.

COMBINEVALUES for composite keys

When building composite key columns for DirectQuery relationships, always use COMBINEVALUES instead of &. This enables the engine to push individual column filters to the source rather than string comparison.

Prefer measures over calculated columns

A calculated column storing concatenated text inflates model size for every row. A measure calculates on demand for only the visible rows. Use measures unless you need the value for relationships or row-level security.

Avoid CONCATENATEX in visuals with many cells

CONCATENATEX(TOPN(10, BigTable, [Sales]), [Name] & ": " & FORMAT([Sales], "$#,##0"), ", ")

FORMAT is expensive per row. Reduce the table first with TOPN or FILTER, then FORMAT the smaller result set.

DAX Text Function Quick Reference

Function	Arguments	Use Case	Recommendation
CONCATENATE	2 only	Join two text values	Avoid — use & instead
& Operator	Unlimited	General text joining	Preferred for all concatenation
CONCATENATEX	Table + expression + delimiter	Aggregate text across rows	Use with filtered/small tables
COMBINEVALUES	Delimiter + 2+ expressions	Composite relationship keys	Required for DirectQuery keys
FORMAT	Value + format string	Number/date to formatted text	Apply last, after aggregation
UNICHAR(10)	Character code	Line breaks in card visuals	Use for multi-line labels

Frequently Asked Questions

How do you concatenate text in Power BI?

The most common way to concatenate text in Power BI is with the ampersand (&) operator. For example: Full Name = [FirstName] & " " & [LastName]. You can also use the CONCATENATE function — CONCATENATE([FirstName], [LastName]) — but it only accepts two arguments. For combining values from multiple rows in a table, use CONCATENATEX. The & operator is the recommended approach because it supports unlimited arguments, is more readable, and performs identically to CONCATENATE.

What is the difference between CONCATENATE and the & operator in Power BI?

CONCATENATE accepts exactly two arguments: CONCATENATE(text1, text2). To join three or more values, you must nest calls: CONCATENATE(CONCATENATE(a, b), c). The & operator has no argument limit: a & b & c & d works directly. Performance is identical — the DAX engine treats them the same way internally. Microsoft recommends the & operator for readability. The only reason CONCATENATE still exists is backward compatibility with Excel formula habits.

How does CONCATENATEX work in Power BI?

CONCATENATEX iterates over a table and concatenates the result of an expression for each row, with an optional delimiter. Syntax: CONCATENATEX(table, expression, delimiter, orderBy, order). Example: CONCATENATEX(Products, [ProductName], ", ") returns "Widget, Gadget, Tool". It is the only DAX function that can aggregate text values across rows — SUM and AVERAGE do not work on text. Use it for comma-separated lists, dynamic labels, and summary strings.

What is COMBINEVALUES in Power BI and when should I use it?

COMBINEVALUES joins two or more text values with a specified delimiter: COMBINEVALUES(",", [Col1], [Col2], [Col3]). Unlike the & operator, COMBINEVALUES is optimized for DirectQuery composite key relationships. When used in a calculated column that serves as a relationship key, Power BI can fold the join back to the source database as separate WHERE clauses instead of a string comparison. Use COMBINEVALUES whenever you need composite keys in DirectQuery or dual storage mode models.

How do you concatenate text and numbers in Power BI?

The & operator automatically converts numbers to text: "Revenue: $" & [SalesAmount] works but displays raw numbers without formatting. For formatted output, wrap numbers with FORMAT: "Revenue: " & FORMAT([SalesAmount], "$#,##0.00"). Common format strings: "#,##0" for integers, "0.0%" for percentages, "MM/DD/YYYY" for dates. Note that FORMAT returns text, so the result cannot be used in further numerical calculations.

Can you concatenate columns from different tables in Power BI?

Yes, you can concatenate columns from different tables using the & operator or CONCATENATE in a measure. Example: Full Info = RELATED(Customer[Name]) & " — " & Sales[Product]. In a measure, ensure proper filter context: Full Label = SELECTEDVALUE(Customer[Name]) & ": " & FORMAT(SUM(Sales[Amount]), "$#,##0"). When referencing another table in a calculated column, use RELATED (many-to-one) or RELATEDTABLE (one-to-many) to traverse relationships.

How do you create dynamic titles in Power BI using concatenation?

Create a measure that builds the title string dynamically: Dynamic Title = "Sales Report — " & SELECTEDVALUE(Date[Year], "All Years") & " | " & SELECTEDVALUE(Region[Name], "All Regions"). Then set the visual title to use this measure via conditional formatting (fx button). This updates the title automatically when users interact with slicers. For chart subtitles showing totals: "Total: " & FORMAT(SUM(Sales[Revenue]), "$#,##0") & " across " & COUNTROWS(VALUES(Product[Category])) & " categories".

What are common mistakes with CONCATENATE in Power BI?

The top mistakes are: 1) Nesting CONCATENATE instead of using & — makes code unreadable. 2) Forgetting to handle BLANK values — "Hello " & BLANK() returns "Hello " with a trailing space; use IF(ISBLANK()) checks. 3) Using FORMAT inside CONCATENATEX on large tables — FORMAT is expensive and should be applied after aggregation. 4) Creating composite keys with & instead of COMBINEVALUES — loses DirectQuery optimization. 5) Concatenating in calculated columns when a measure would work — inflating model size. 6) Not adding separators — [First] & [Last] returns "JohnSmith" instead of "John Smith".

How do you handle BLANK values when concatenating in Power BI?

BLANK values in concatenation produce unexpected results: "Name: " & BLANK() returns "Name: " (trailing space if you added one). Best practices: 1) Use IF: IF(ISBLANK([Middle]), [First] & " " & [Last], [First] & " " & [Middle] & " " & [Last]). 2) Use COALESCE: [First] & " " & COALESCE([Middle], "") & " " & [Last]. 3) For CONCATENATEX, filter blanks first: CONCATENATEX(FILTER(Table, NOT ISBLANK([Col])), [Col], ", "). Always test edge cases with blank data to avoid orphaned delimiters or extra spaces.

Need Help with Complex DAX or Power BI Architecture?

EPC Group's Power BI consultants build enterprise DAX models for Fortune 500 organizations. From performance optimization to complex text manipulation patterns, we deliver production-ready solutions.

Schedule a Consultation Explore Power BI Services