locked
DAX: Concatenate multiple values for unique ID's RRS feed

  • Question

  • Hi all, 

    My table is "Query."  How do I concatenate all the [Color] column values for each [Name]?

    The calculated column that I want is [Total].  (With one space in between each word)

    Name Color Total
    John Red Red Blue Orange Green
    John Blue Red Blue Orange Green
    John Orange Red Blue Orange Green
    John Green Red Blue Orange Green
    Steve Red Red Orange Green
    Steve Orange Red Orange Green
    Steve Green Red Orange Green
    Mark Red Red

    Thanks much!

    ~UG

    Thursday, February 4, 2016 3:26 PM

Answers

  • In Excel 2016 or Power BI Desktop you can use the CONCATENATEX function:

    =CONCATENATEX(FILTER(ALL(Query),Query[Name]=EARLIER([Name])),[Color]," ")

    I wouldn't know how to do this in Excel 2013.

    • Marked as answer by undergrads1 Friday, February 5, 2016 9:38 PM
    Thursday, February 4, 2016 5:09 PM
    Answerer

All replies