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

[dinesharivalaga]

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

[v-xianjtan-msft · Accepted solution]

Hi @dinesharivalaga 

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.

[dinesharivalaga]

@v-xianjtan-msft  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 🙂

[ahadkarimi]

Hi @dinesharivalaga, 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!

[dinesharivalaga]

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

[dinesharivalaga]

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