Forum Discussion

khappersett's avatar
khappersett
Icon for Resolver I rankResolver I
9 years ago
Solved

Concatenate Columns If Same ID

I have a table with rows for item ID, catalog name, and catalog year. Some items are released in multiple catalogs so they have multiple rows. I am trying to create a new measure that will concatenat...
Need Help
  • P3Tom's avatar
    P3Tom
    9 years ago

    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

     

  • Anonymous's avatar
    Anonymous
    9 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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

ewkcoder's avatar
ewkcoder
Frequent Visitor
3 years ago

This worked for my use case.  I extended this with an additional condition to exclude a field value from being included in the concatenation.

All Year = CONCATENATEX(FILTER(ALL('sample'),[Item ID]=EARLIER('sample'[Item ID]) && [Item ID]<>"Not Supplied"),[Catalog Year],",")