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
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

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?

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

        Hi,

         

        I have done this using Power Query and then pushed the data into the Data Model.  Will this be OK with you?