Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Concatenate 2 different text rows based on the other column(s)

Hi Experts,

I am working on the scenario that to merge 2 "comments" into the single row if the comments are different content.

Below the table i am using for my DB and it has 2 same account names (masked) but forecast,revenue,cm% and comments are different values.

 

 

In this case as the account names are same , then the "comments" should show in a single row , rest all the columns will summing up (already those requirement is done)

 

Now i want to merge "Comments" as below :

Expected result : Same Account name so single row , different forecase,revenue and CM% so it summing up the values and for CM% we have already applied the logic , only the comments we need to merge in this condition.

 

 

Please help to achieve this ..

 

Thanks

DK

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous 

     

    The "This is not works and comments are repeating in a single rows" you mentioned is probably because you have used "New Comments = CONCATENATEX('Test Delivery Updates',VALUES('Test Delivery Updates'[Comments]),"&")" to create calculated column instead of measure.

     

    If you want to create a calculated column, use the following DAX:

     

    MergedComments = 
CALCULATE(
    CONCATENATEX(
        DISTINCT('Test Delivery Updates'[Comments]),
        'Test Delivery Updates'[Comments],
        " & "
    ),
    ALLEXCEPT('Test Delivery Updates', 'Test Delivery Updates'[Account Name])
)

     

     

    If you want to create a table, use the following DAX:

     

    MergedTable = 
SUMMARIZE(
    'Test Delivery Updates',
    'Test Delivery Updates'[Account Name],
    "MergedComments", CONCATENATEX(
        FILTER(
            'Test Delivery Updates',
            'Test Delivery Updates'[Account Name] = EARLIER('Test Delivery Updates'[Account Name])
        ),
        'Test Delivery Updates'[Comments],
        "&"
    )
)

     

     

    Here are my simple test results:

     

    Best Regards,
    Jarvis Tang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

5 Replies

  • ahadkarimi's avatar
    ahadkarimi
    Icon for Solution Specialist rankSolution Specialist
    1 year ago

    Hi Anonymous, try two measures below, and if you encounter any issues, let me know.

    Total Forecast = SUM(Table[Forecast])
Total Revenue = SUM(Table[Revenue])
Total CM% = --Your logic for CM%
    Merged Comments = CONCATENATEX(
    VALUES(Table[Comments]),
    Table[Comments],
    " & "  -- Use any delimiter you prefer
)

    Did I answer your question? If so, please mark my post as the solution! ✔️
    Your Kudos are much appreciated! Proud to be a Solution Specialist!

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      ahadkarimi  Thanks for your response 🙂

      New Comments = CONCATENATEX('Test Delivery Updates',VALUES('Test Delivery Updates'[Comments]),"&")
       
      This is not works and comments are repeating in a single rows.
       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        ahadkarimi  FYI : I have applied an Unpivot column method for the selected columns in the dataset , so after that all the rows were duplicated with unpivot data .
        Now each account has 27 rows post unpivot selected columns applied .

         

        Hence it is showing different errors now with some other approached i did .

        DAX : Merged Comments = SUMMARIZE('Test Delivery Updates','

        Test Delivery Updates'[Account Name],

        "New Comments",

        CONCATENATEX('Test Delivery Updates',

        'Test Delivery Updates'[Comments] ,"&",'Test Delivery Updates'[Comments],ASC

        ) )

        Error -- "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous 

     

    The "This is not works and comments are repeating in a single rows" you mentioned is probably because you have used "New Comments = CONCATENATEX('Test Delivery Updates',VALUES('Test Delivery Updates'[Comments]),"&")" to create calculated column instead of measure.

     

    If you want to create a calculated column, use the following DAX:

     

    MergedComments = 
CALCULATE(
    CONCATENATEX(
        DISTINCT('Test Delivery Updates'[Comments]),
        'Test Delivery Updates'[Comments],
        " & "
    ),
    ALLEXCEPT('Test Delivery Updates', 'Test Delivery Updates'[Account Name])
)

     

     

    If you want to create a table, use the following DAX:

     

    MergedTable = 
SUMMARIZE(
    'Test Delivery Updates',
    'Test Delivery Updates'[Account Name],
    "MergedComments", CONCATENATEX(
        FILTER(
            'Test Delivery Updates',
            'Test Delivery Updates'[Account Name] = EARLIER('Test Delivery Updates'[Account Name])
        ),
        'Test Delivery Updates'[Comments],
        "&"
    )
)

     

     

    Here are my simple test results:

     

    Best Regards,
    Jarvis Tang
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Anonymous  Thanks a lot for your solution 🙂

      I have taken calculated column method and it works fine and merging well .

      Saved me 🙂

      Morning starts with good news 🙂

      Thanks Again 🙂