topic Re: Concatenate 2 different text rows based on the other column(s) in DAX Commands and Tips

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

Anonymous — Mon, 23 Sep 2024 05:41:42 GMT

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

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

ahadkarimi — Mon, 23 Sep 2024 14:59:23 GMT

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!

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

Anonymous — Tue, 24 Sep 2024 05:57:05 GMT

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.

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

Anonymous — Tue, 24 Sep 2024 09:16:00 GMT

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."

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

Anonymous — Wed, 25 Sep 2024 05:39:06 GMT

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.

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

Anonymous — Wed, 25 Sep 2024 05:44:53 GMT

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 🙂