none
Concatenating multiple rows in one row with a delimiter RRS feed

  • Question

  • Hi,

    I am fairly new to DAX. Unable to figure out an approach to achieve the concatenation of row value with a delimiter

    For eg:

    Data:

    Country  Sales Personnel
    A Tom
    A Jack
    A Vincent
    B Rick
    B Jon
    C Tim
    C Jill

    Output:

    Country  Sales Personnel
    A Tom, Jack, Vincent
    B Rick, Jon
    C Tim, Jill

    Tuesday, February 27, 2018 2:03 PM

Answers

  • Hi Abhi1318,

    Thanks for your question.

    In this scenario, please try to use DAX function CONCATENATEX.
    CONCATENATEX(Tablename,Comments,"Delimeter whatever u want")

    If you want to create a calculated column, please try below sample DAX formula:

    ConcatenateCol =
    CONCATENATEX (
        FILTER ( Table1, Table1[Dep] = EARLIER ( Table1[Dep] ) ),
        Table1[Name],
        ","
    )

    If you want to create a Measure, please try below samle DAX formula:

    ConcatenateMeasure =
    CONCATENATEX (
        FILTER ( Table1, Table1[Dep] = MAX ( Table1[Dep] ) ),
        Table1[Name],
        ","
    )


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by alexander fun Wednesday, February 28, 2018 10:56 AM
    • Marked as answer by Abhi1318 Wednesday, February 28, 2018 1:50 PM
    Wednesday, February 28, 2018 1:39 AM
    Moderator

All replies

  • Excel 2010/2013/2016 Power Query (aka Get & Transform)
    Concatenate with custom delimiter. Automatically sorted.
    PQ loads dynamically to PowerPivot in 2016.
    http://www.mediafire.com/file/84gj47suso10hln/02_27_18.xlsx
    http://www.mediafire.com/file/ya7d485ba2tx4b7/02_27_18.pdf

    Tuesday, February 27, 2018 3:51 PM
  • Hi Abhi1318,

    Thanks for your question.

    In this scenario, please try to use DAX function CONCATENATEX.
    CONCATENATEX(Tablename,Comments,"Delimeter whatever u want")

    If you want to create a calculated column, please try below sample DAX formula:

    ConcatenateCol =
    CONCATENATEX (
        FILTER ( Table1, Table1[Dep] = EARLIER ( Table1[Dep] ) ),
        Table1[Name],
        ","
    )

    If you want to create a Measure, please try below samle DAX formula:

    ConcatenateMeasure =
    CONCATENATEX (
        FILTER ( Table1, Table1[Dep] = MAX ( Table1[Dep] ) ),
        Table1[Name],
        ","
    )


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by alexander fun Wednesday, February 28, 2018 10:56 AM
    • Marked as answer by Abhi1318 Wednesday, February 28, 2018 1:50 PM
    Wednesday, February 28, 2018 1:39 AM
    Moderator
  • Hi Wilson,

    Many thanks for the quick response. It works. 

    However, there is a miss from my side, in giving the scenario. For my data set, the values in Dep column repeats, for example:

    Country Sales Personnel
    A Tom
    A Jack
    A Vincent
    B Jon
    C Tim
    C Jill
    D Donald
    D Trump
    D Donald
    D Donald

    In that case the output should be:

    Country Sales Personnel
    A Tom, Jack, Vincent
    B Rick, Jon
    C Tim, Jill
    D Donald, Trump

    instead of 

    C
    Country Sales Personnel
    A Tom, Jack, Vincent
    B Rick, Jon
    C Tim, Jill
    D Donald, Trump, Donald, Donald

    Any suggestion how to get only the distinct values?

    Thanks in advance!

    Wednesday, February 28, 2018 9:41 AM
  • Hi Abhi1318,

    Thanks for your response.

    >>>Any suggestion how to get only the distinct values?

    In this scenario, please try below DAX:

    DistinctConcatenate = CONCATENATEX(DISTINCT(Table1[Name]),Table1[Name],",")

    One more thing, please kindly mark the helpful replies as answers. By doing so, it will benefit all comunity members who are facing this similar issues. Your contribution is highly appreciated.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by alexander fun Wednesday, February 28, 2018 10:56 AM
    Wednesday, February 28, 2018 9:58 AM
    Moderator
  • Not sure, if I followed correctly, but I am getting incorrect result

    

    I'm using following function:

    DistinctConcatenate1 = CONCATENATEX(DISTINCT('Test table'[Dep]),'Test table'[X],",")

    Dep X DistinctConcatenate1
    A Tom, Jack, Vincent Tom, Jack, Vincent,Tom, Jack, Vincent,Tom, Jack, Vincent
    B Rick, Jon Rick, Jon,Rick, Jon,Rick, Jon
    C Tim, Jill, Jill, Jill Tim, Jill, Jill, Jill,Tim, Jill, Jill, Jill,Tim, Jill, Jill, Jill
    Wednesday, February 28, 2018 2:32 PM
  • Hi Abhi1318,

    Thanks for your response.

    >>>Not sure, if I followed correctly, but I am getting incorrect result

    Please try below DAX formula:

    DistinctConcatenate1 = CONCATENATEX(DISTINCT('Test table'[x]),'Test table'[X],",")


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, March 1, 2018 12:50 AM
    Moderator
  • Hi Willson,

    When I try the above DAX; my query goes into infinite loop. The report doesn't seem to refresh. It keeps refreshing.

    DistinctConcatenate1 = CONCATENATEX(DISTINCT('Test table'[x]),'Test table'[X],",")

     

    Any Clues?


    Abhi1318

    Thursday, March 1, 2018 11:17 AM
  • Solved for text values

    DistinctConcatinate = 
        CONCATENATEX(
            FILTER(
                SUMMARIZE(
                    Table1;
                    Table1[Dep];
                    Table1[Name]); 
                Table1[Dep] = EARLIER(Table1[Dep]);
            Table1[Name];
            ", ")


    • Edited by vygerasim Wednesday, March 13, 2019 12:07 PM
    Wednesday, March 13, 2019 11:47 AM
  • Thanks so much Wilson!!!
    Tuesday, December 31, 2019 1:01 AM
  • Thanks so much Wilson!!!!  I spent several days trying to achieve the result you so eloquently achieved with a very smart measure!!!!
    Tuesday, December 31, 2019 1:03 AM