Forum Discussion

Wkeith's avatar
Wkeith
Helper II
7 years ago
Solved

Duplicate Concatenate Question

Hi, I am trying to concatenate products that belong to the same order ID to do some market basket analysis. So for example, lets say for an order number that has these products sold in it: Milk, Bread, Milk, and Eggs (the reason why there is two milks is because a customer bought two different brands of milk). I am trying to concatenate these values in a new calculated column in my sales table to be, "Milk-Bread-Eggs" but right now with my current forumla: 

 

Market Basket =
CALCULATE(
CONCATENATEX(Sales, Sales[Product], “-“ ),
FILTER(
ALL(Sales),
Sales[Transaction ID] = EARLIER(Sales[Transaction ID])
)
)

The result I am getting is, "Milk-Bread-Milk-Eggs". But I don't want the Milk to show twice in my new column, I only want it to appear once.

 

Does anyone know a way I can edit my current forumla to adjust for this issue?

 

Thanks! Cmcmahan 

 

Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    Hi Wkeith ,

     

    Try to change you formula to something similar to this:

     

    Market Basket =
CALCULATE(
CONCATENATEX(DISTINCT(Sales[Product]), Sales[Product], “-“ ),
FILTER(
ALL(Sales),
Sales[Transaction ID] = EARLIER(Sales[Transaction ID])
)
)

    Regards,

    MFelix

2 Replies

  • MFelix's avatar
    MFelix
    Super User
    7 years ago

    Hi Wkeith ,

     

    Try to change you formula to something similar to this:

     

    Market Basket =
CALCULATE(
CONCATENATEX(DISTINCT(Sales[Product]), Sales[Product], “-“ ),
FILTER(
ALL(Sales),
Sales[Transaction ID] = EARLIER(Sales[Transaction ID])
)
)

    Regards,

    MFelix