Forum Discussion

khappersett's avatar
khappersett
Resolver 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

filipe197's avatar
filipe197
Frequent Visitor
8 years ago

Hi Ashish_Mathur

 

I dont use power query and even if i want to, i can't install it in my pc :( can you just tell me the dax formula you've written to get de outcome of "All Year" column?

 

Many thanks,

filipe197

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi,

 

Power Query comes with the Power BI desktop (Home > Edit Queries.  The Query Editor window opens up is Power Query).  Download the PowerBI desktop solution from here.

 

Hope this helps.