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

  • P3Tom's avatar
    P3Tom
    Icon for Helper I rankHelper I
    9 years ago

    Although this topic is titled, "Concatenate Columns If Same ID", later in the original request it says:

     

    "Ultimately, I am trying to find a way to count the items that are in a catalog for one year, but not another."

     

    Restated, concatenation was a means used to count the items that are in a catalog only for one year, but not another.

     

    A complete solution will also give an answer to the ultimate end (what is the count of catalog items that were listed for only one year).

     

    Tom

  • filipe197's avatar
    filipe197
    Frequent Visitor
    8 years ago

    Hello Anonymous 

     

    What if additionally you have for the same Item ID duplicate Catalog Year? How could you consider in your formula only the unique values? I mean, if item ID "A" have in column "Catalog Year" the values 2012,2012 and 2014 i just want to consider in "All year" this result: 2012,2014. 

     

    Thanks,

    filipe197

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      8 years ago

      Hi filipe197,

       

      Do you want that as a calculated column formula or as a measure?

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