Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Return all customer products

Dear,

 

Data sample:

 

Customer ID         Product       Purchase Date 

1                             Apple            01-01-2020

2                             Pie                 02-02-2020

1                             Chips             05-01-2020

2                             Bread             03-04-2020

1                             Cheese           05-20-2020

 

 

What I would like to do is to add a column that adds up (divided by ",") all products bought by that customer, preferably based on order date. It should look as followed:

 

Customer ID         Product       Purchase Date           CustomerProducts

1                             Apple            01-01-2020              Apple, Chips, Cheese

2                             Pie                 31-07-2020              Bread, Pie

1                             Chips             05-01-2020              Apple, Chips, Cheese

2                             Bread             03-04-2020              Bread, Pie

1                             Cheese           05-20-2020              Apple Chips, Cheese

 

I'd like to hear what the possibilities are! 🙂 

Kind regards,

Daniël

Need Help
Tips and Tricks
  • v-yingjl's avatar
    v-yingjl
    6 years ago

    Hi Anonymous ,

    In CONCATENATEX() function, you can use the optional parameter [Order by] to sort the string as your needed, the calculated column is like this:

    Re = 
CONCATENATEX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Customer ID] = EARLIER ( 'Table'[Customer ID] )
    ),
    'Table'[Product],
    ",",
    'Table'[Purchase Date], ASC
)

    Attached a sample file in the below, hopes to help you.

     

    Best Regards,
    Yingjie Li

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , Try a measure like

    concatenatex(filter(allselected(Table), table[Customer ID] = max(Table[Customer ID])) , Product )

     

    or column like

    concatenatex(filter(allselected(Table), table[Customer ID] = earlier(Table[Customer ID])) , Product )

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      That calculated column worked, nice! 🙂

       

      Any chance I can add logic to it, where it puts the first product first, based on purchase date?

      In the table, the products currently aren't listed in that order.

       

      I'd like to hear it!

      • v-yingjl's avatar
        v-yingjl
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi Anonymous ,

        In CONCATENATEX() function, you can use the optional parameter [Order by] to sort the string as your needed, the calculated column is like this:

        Re = 
CONCATENATEX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Customer ID] = EARLIER ( 'Table'[Customer ID] )
    ),
    'Table'[Product],
    ",",
    'Table'[Purchase Date], ASC
)

        Attached a sample file in the below, hopes to help you.

         

        Best Regards,
        Yingjie Li

        If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

  • tex628's avatar
    tex628
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Try this,

    CustomerProducts = 
VAR Customer = SELECTEDVALUE('Table'[Customer ID])
Return
CALCULATE(CONCATENATEX( 'Table', 'Table'[Product] , ", ") , ALL('Table') , 'Table'[Customer ID] = Customer)