DAX formula to concatenate three columns

Question

I am mew to DAX. How can I concatenate three different columns say First_Name, Middle_Name and Last_Name to a single column with a space in between using CONCATENATE function in DAX. At present I could concatenate only two columns.

=CONCATENATE(FIRST_NAME],CONCATENATE(" ",[LAST_NAME]))

If any other function is there please do let me know that also. My aim is to concatenate 1st Name, 2nd Name and 3rd Name into a single column in SSAS Tabular Model

Please help me. Thanks in Advance

score 17 · Accepted Answer · answered Mar 04 '14 at 15:56

17

If the columns are already text, you can use th & operator, i. e. use

[FIRST_NAME] & " " & [LAST_NAME]

You can find the documentation of concatenate - including the fact that it only accepts two arguments - at http://technet.microsoft.com/en-us/library/ee634811.aspx.

answered Mar 04 '14 at 15:56

FrankPl

12,845
2
12
39

1

Thank You very much. The '&' served the purpose. – user2107971 Mar 05 '14 at 05:52

score 1 · Answer 2 · answered Apr 13 '18 at 14:38

1

You can use nested CONCATENATE functions with the & to append your spaces. Following your example:

=CONCATENATE([FIRST NAME]&" ", CONCATENATE([MIDDLE NAME]&" ", [LAST NAME]))

answered Apr 13 '18 at 14:38

userfl89

4,134
1
7
16

CONCATENATE is not available in DAX – Sam Dec 06 '18 at 15:35
3

@Sam are you sure? This function is available in DaxStudio (I'm using version 2.7.4) and the Microsoft reference for it is here: https://docs.microsoft.com/en-us/dax/concatenate-function-dax – userfl89 Dec 06 '18 at 15:37

score 1 · Answer 3 · answered May 06 '20 at 08:37

For anyone coming back to this, particularly if you have more than two variables to concatenate, consider using COMBINEVALUES():

=COMBINEVALUES(<delimiter>, <expression>, <expression>[, <expression>]…)

For the posted question, this would look like:

=COMBINEVALUES(" ", [FIRST NAME], [LAST NAME])

And can be expanded to include additional values after [LAST NAME].

DAX formula to concatenate three columns

3 Answers3