Concatenating comments based on unique values

djk123 — Mon, 16 Jan 2023 16:19:12 GMT

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.

Re: Concatenating comments based on unique values

Jihwan_Kim — Mon, 16 Jan 2023 18:08:56 GMT

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], ", " )

topic Concatenating comments based on unique values in DAX Commands and Tips

Concatenating comments based on unique values

Re: Concatenating comments based on unique values