How to concatenate a list of products by customer (product combos)

[danielgnic]

Hello, I have of list of transactions by customers like this:

Customer ID	Product
1	A
1	B
1	C
2	A
3	A
3	C
4	A
4	D
5	B

I need to figure out the combinations of products by customer, something like this:

Customer ID	Product Combo
1	A, B, C
2	A
3	A, C
4	A, D

Also, I would like to be able to filter this product combos considering just customers who bought product "A" or product "A" and other.

Finally, I expect to have a visualization with this information:

Top Product Combos	Top Product Combos
A, B	56
A, C	34
A	23
A, D	16

Top product combos is the frequence in which a customer buys "A,B".

Thank you in advance 🙂

[v-rongtiep-msft]

Hi @danielgnic ,

Sorry I am foolish. In the last table the value of A,B is 56, how is it calculated? And there is the value A,B,C in the second table, why it disappeared in the last table? I can understand the data in the second table, but I am very confused about the third table.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

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

[danielgnic]

Hello, @v-rongtiep-msft , thank you for your reply. The numbers in the last table were an example, I'm sorry for not being totally clear about it. Concerning the "A, B, C" combo, you're right, it disappeared but I didn't mean to do it lol. 

This is what I have:

And this is what I would like to calculate:

IAs you can see, I'm not counting customer 4 because they didn't buy product A

And finally:

[v-rongtiep-msft · Accepted solution]

Hi @danielgnic ,

I have created a simple smaple, please refer to it to see if it helps you.

Create a column first.

Column = VAR _1= CONCATENATEX(FILTER('Table','Table'[customer id]=EARLIER('Table'[customer id])),'Table'[product],",")
RETURN
IF(CONTAINSSTRING(_1,"A"),_1,BLANK())

Then create a measure.

Measure = CALCULATE(DISTINCTCOUNT('Table'[customer id]),FILTER(ALL('Table'),'Table'[Column]=SELECTEDVALUE('Table'[Column])))

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

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