Concatenating comments based on unique values

[djk123]

Hello,

I am relatively new to PowerBI so apologies the question is not framed well.

I have the following dataset (simplified for ease). I want to combine the comments where the city and measure are the same. However, I need maintain all rows in the table. I have tried various

CONCATENATEX variations but have had no luck.

City	Measure	Comments
Manchester	Sales	Good
Manchester	Sales	Bad
Manchester	Expenditure	Poor
Manchester	Expenditure	Excellent
London	Sales	Good
London	Sales	Poor
London	Expenditure	Poor
London	Expenditure	Good

My desired output is

City	Measure	Comments on performance	NewColumn
Manchester	Sales	Good	Good, Bad
Manchester	Sales	Bad	Good, Bad
Manchester	Expenditure	Poor	Poor, Excellent
Manchester	Expenditure	Excellent	Poor, Excellent
London	Sales	Good	Good, Poor
London	Sales	Poor	Good, Poor
London	Expenditure	Poor	Poor, Good
London	Expenditure	Good	Poor, Good

Many thanks in advance.

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

Expected result CC =
CONCATENATEX (
    FILTER (
        'Table',
        'Table'[City] = EARLIER ( 'Table'[City] )
            && 'Table'[Measure] = EARLIER ( 'Table'[Measure] )
    ),
    'Table'[Comments],
    ", "
)