Duplicate Concatenate Question

[Wkeith]

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 

[MFelix · Accepted solution]

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

[Wkeith]

Thanks! You rock.