Concatenate Columns If Same ID

Since your data set did not have any items that were in only 1 year, I created a similar set which you can find in this linked pbix file.

Multiple table model is required to get the results above.

Here are the measures:

Count If Item Sold in Only 1 Year =
COUNTROWS ( FILTER ( 'Items', [Item Years in Catalog] = 1 ) )

Item Catalog Years =
IF (
    NOT ( ISBLANK ( SELECTEDVALUE ( Items[Item ID] ) ) ),
    CONCATENATEX ( RELATEDTABLE ( Data ), Data[Catalog Year], ", " )
)

Item Total Years in Catalog =
CALCULATE (
    COUNTROWS ( VALUES ( 'Years'[Year] ) ),
    CROSSFILTER ( Data[Catalog Year], Years[Year], BOTH )
)

Tom

www.powerpivotpro.com

Hi khappersett,

I think you need to filter the records which has the same id, then use this as the source of concatenate function.

All Year = CONCATENATEX(FILTER(ALL('sample'),[Item ID]=EARLIER('sample'[Item ID])),[Catalog Year],",")

Regards,

Xiaoxin Sheng