COMBINEVALUES DAX Function (Text)

Combines the given set of operands using a specified delimiter.

Syntax

COMBINEVALUES ( <Delimiter>, <Expression1>, <Expression2> [, <Expression2> [, … ] ] )
Parameter Attributes Description
Delimiter

Delimiter which is used to join the expressions into a single string.

Expression1

First Expression to be evaluated and joined with other strings using the Separator.

Expression2 Repeatable

Second Expression to be evaluated and joined with other strings using the Separator.

Return values

Scalar A single string value.

The concatenated string.

Remarks

The COMBINEVALUES function assumes, but does not validate, that when the input values are different, the output strings are also different. Based on this assumption, when COMBINEVALUES is used to create calculated columns in order to build a relationship that joins multiple columns from two DirectQuery tables, an optimized join condition is generated at query time.

For example, if users want to create a relationship between Table1(Column1, Column2) and Table2(Column1, Column2), they can create two calculated columns, one on each table, as:

Table1[CalcColumn] = COMBINEVALUES ( ",", Table1[Column1], Table1[Column2] )

and

Table2[CalcColumn] = COMBINEVALUES ( ",", Table2[Column1], Table2[Column2] )

And then create a relationship between Table1[CalcColumn] and Table2[CalcColumn]. Unlike other DAX functions and operators, which are translated literally to the corresponding SQL operators and functions, the above relationship generates a SQL join predicate as:

(Table1.Column1 = Table2.Column1 OR Table1.Column1 IS NULL AND Table2.Column1 IS NULL)

and

(Table1.Column2 = Table2.Column2 OR Table1.Column2 IS NULL AND Table2.Column2 IS NULL).

The join predicate can potentially deliver much better query performance than one that involves complex SQL operators and functions.

The COMBINEVALUES function relies on users to choose the appropriate delimiter to ensure that unique combinations of input values produce distinct output strings but it does not validate that the assumption is true. For example, if users choose “|” as the delimiter, but one row in Table1 has Table1[Column1] = “|” and Table1[Column2] = ” “, while one row in Table2 has Table2[Column1] = “” and Table2[Column2] = “| “, the two concatenated outputs will be the same “|| “, which seem to indicate that the two rows are a match in the join operation. The two rows are not joined together if both tables are from the same DirectQuery source although they are joined together if both tables are imported.

» 2 related articles

Examples

--  COMBINEVALUES concatenates columns using a separator. 
--  The result is a simple string concatenation.
--  Its usage is in DirectQuery over SQL models. The DAX engine can 
--  generate optimized JOIN conditions for calculated columns created 
--  with COMBINEVALUES, in contrast with simple string concatenation.
EVALUATE
ADDCOLUMNS (
    SUMMARIZE (
        TOPN ( 5, Customer ),
        Customer[Customer Code],
        Customer[Customer Name],
        Customer[CountryRegion]
    ),
    "Name, Code, and Country combined",
        COMBINEVALUES (
            " | ",
            Customer[Customer Name],
            Customer[Customer Code],
            Customer[CountryRegion]
        )
)
Customer Code Customer Name CountryRegion Name, Code, and Country combined
11024 Xie, Russell United States Xie, Russell | 11024 | United States
11036 Russell, Jennifer United States Russell, Jennifer | 11036 | United States
11041 Carter, Amanda United States Carter, Amanda | 11041 | United States
11043 Simmons, Nathan United States Simmons, Nathan | 11043 | United States
11928 Morris, Isabella United States Morris, Isabella | 11928 | United States

Related articles

Learn more about COMBINEVALUES in the following articles:

Last update: Mar 13, 2024   » Contribute   » Show contributors

Contributors: Alberto Ferrari, Marco Russo, Srivatshan

Microsoft documentation: https://docs.microsoft.com/en-us/dax/combinevalues-function-dax

2018-2024 © SQLBI. All rights are reserved. Information coming from Microsoft documentation is property of Microsoft Corp. » Contact us   » Privacy Policy & Cookies

Context Transition

This function performs a Context Transition if called in a Row Context. Click to read more.

Row Context

This expression is executed in a Row Context. Click to read more.

Iterator

Not recommended

The use of this function is not recommended. See Remarks and Related functions for alternatives.

Not recommended

The use of this parameter is not recommended.

Deprecated

This function is deprecated. Jump to the Alternatives section to see the function to use.

Volatile

A volatile function may return a different result every time you call it, even if you provide the same arguments. Click to read more.

Deprecated

This parameter is deprecated and its use is not recommended.

DirectQuery compatibility

Limitations are placed on DAX expressions allowed in measures and calculated columns.
The state below shows the DirectQuery compatibility of the DAX function.

Contribute

Want to improve the content of COMBINEVALUES? Did you find any issue?
Please, report it us! All submissions will be evaluated for possible updates of the content.


This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.