CONCATENATEX

An expression's text for each row of a table, joined with a delimiter, optionally in an order; BLANK over no rows.

Group: Aggregation. Compatible with DAX.

Syntax

CONCATENATEX ( <table>, <expression> [, <delimiter>] [, <orderBy_expression>, <order>] … )

Parameter	Type	Description
`table`	Table	The rows whose texts are joined.
`expression`	Expression evaluated for each row	The text produced for each row; a number or date is turned into text.
`delimiter`	Text optional	What goes between two texts. Without it, the texts are joined with nothing between them.
`orderBy_expression`	Expression optional, repeatable, evaluated for each row	A value worked out for each row that sets the order of the texts.
`order`	Keyword optional, repeatable	`ASC` for smallest first or `DESC` for largest first, for the order value before it; `DESC` when left out.

Returns

Text. The texts of all rows, one after another with the delimiter between them.

Remarks

Several order values may follow one another, each with its own ASC or DESC: the second decides between rows the first leaves tied.
Without an order, OpenBI joins the values of VALUES or DISTINCT from the smallest up, and a table's rows in the order they were loaded. Give an order whenever the result is shown to someone, so it stays the same.
A blank result for a row still takes its place: rows x, blank and z joined with | give x||z.
Over an empty table the result is BLANK, not empty text.
Text is compared without regard to upper and lower case when it is ordered.

Compared with DAX

Without an order, Power BI Desktop joins the texts in the order its storage holds the rows, which follows the data only in part and can't be told from the model: the same table can come out in another order than OpenBI's. With an order, both give the same text.

Examples

Products from the smallest volume up

EVALUATE
VAR Orders =
    DATATABLE (
        "Product", STRING, "Liters", INTEGER,
        { { "Pils", 120 }, { "IPA", 80 }, { "Stout", 45 } }
    )
RETURN
    ROW ( "Products", CONCATENATEX ( Orders, [Product], ", ", [Liters], ASC ) )

Products
Stout, IPA, Pils

The three shops that need the most crates

Two order values: most crates first, then the district's name for a tie.

EVALUATE
ROW (
    "Shops",
        CONCATENATEX (
            TOPN ( 3, Places, Places[Crates] ),
            Places[District],
            ", ",
            Places[Crates], DESC,
            Places[District], ASC
        )
)

Shops
Grünerløkka, Bjørvika, St. Hanshaugen

No delimiter

EVALUATE
ROW ( "Joined", CONCATENATEX ( DATATABLE ( "Letter", STRING, { { "x" }, { "y" }, { "z" } } ), [Letter] ) )

Joined
xyz

CONCATENATE, COMBINEVALUES, TOPN, VALUES, SUMX.

Last updated 3 November 2026 · OpenBI Studio 0.3.74