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

P3Tom's avatar
P3Tom
Icon for Helper I rankHelper I
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

 

lsuresh89's avatar
lsuresh89
Icon for Helper I rankHelper I
3 years ago

Hi P3Tom, This is an awesome solution. I just have an additional requirement which I'm unable to figure out. Could you help me with a solution to only show unique years in case I have a repitition of years. E.g If in the table above we have A+Spring+2015 twice, it should still give me only one instance while showing "Item catalog years" which in the example above would be 2015,2016,2017 and not 2015,2015,2016,2017.

 

Could you please help me with a way to come up with distinct values? Thanks!