Forum Discussion

djk123's avatar
djk123
New Member
3 years ago

Concatenating comments based on unique values

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.

CityMeasureComments
ManchesterSalesGood
ManchesterSalesBad
ManchesterExpenditurePoor
ManchesterExpenditureExcellent
LondonSalesGood
LondonSalesPoor
LondonExpenditurePoor
LondonExpenditureGood

 

My desired output is

CityMeasureComments on performanceNewColumn
ManchesterSalesGoodGood, Bad
ManchesterSalesBadGood, Bad
ManchesterExpenditurePoorPoor, Excellent
ManchesterExpenditureExcellentPoor, Excellent
LondonSalesGoodGood, Poor
LondonSalesPoorGood, Poor
LondonExpenditurePoorPoor, Good
LondonExpenditureGoodPoor, Good

 

Many thanks in advance.

CONCATENATEX
DAX

1 Reply

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

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