The COMBINEVALUES function joins two or more text strings into a single string by adding a separator between them.
COMBINEVALUES(
delimiter,
expression,
expression
[, expression]...
)
- delimiter: Separator to use between expressions. It must be a constant value.
- expression: DAX expressions whose values will be concatenated in a single string.
The COMBINEVALUES function returns a text string.
The main objective of this function is to support multi-column relationships in DirectQuery models. Thus, for example, if you want to create a relationship between columns c1 and c2 of table1 and columns c1 and c2 of table2, you can create two calculated columns, one in each table, as follows:
table1[calculated-column] = COMBINEVALUES(",", table1[c1], table1[c2])
table2[calculated-column] = COMBINEVALUES(",", table2[c1], table2[c2])
...and create a relationship between table1[calculated-column] and table2[calculated-column].
The COMBINEVALUES function assumes -although it does not validate it- that if the input values (the values of columns c1 and c2 of tables 1 and 2 in the previous example) are different, so will the output values. In this way, if columns c1 and c2 of a row in table 1 contain the values "|" and "" (an empty text string), for example, and columns c1 and c2 of a row in table 2 contain the values "" and "|", if the character "|" is chosen as the delimiter, the concatenation of both columns would be "||" in both tables, which would lead DAX to assume that both rows should be merged together, when they are not. It is the responsibility of the user to choose the correct delimiters to ensure that this combination occurs as expected.
If we start from a Geography table containing information on geographic locations, information that includes the country and the postal code in the Country and PostalCode fields respectively, we can create a calculated Country PostalCode column that concatenates both fields with a comma as a separator with the following measure:
