Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
danielgnic
Frequent Visitor

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

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

danielgnic_0-1678294488583.png

 

 

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

danielgnic_1-1678294514338.png

 

 

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

 

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

 

danielgnic_2-1678294527609.png

 

 

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

 

 

Thank you in advance 🙂

1 ACCEPTED SOLUTION

Hi, @danielgnic 

 

You can try the following methods.
Column:

Product List = 
CONCATENATEX (
    CALCULATETABLE ( VALUES ( 'Table'[Product] ),
        FILTER ( ALL ( 'Table' ),
            [Transaction Type] = EARLIER ( 'Table'[Transaction Type] )
                && [Customer ID] = EARLIER ( 'Table'[Customer ID] ) ) ),
    [Product],
    ","
)

vzhangti_0-1679017380558.png
Measure:

FREQENCE = CALCULATE(DISTINCTCOUNT('Table'[Customer ID]))

vzhangti_1-1679017506691.png

Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

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

View solution in original post

3 REPLIES 3
danielgnic
Frequent Visitor

Hello @v-zhangti , thank you for your reply.

 

Your formula did work, however, I have something like this:

 

danielgnic_0-1678460591475.png

 

I would like to be able to filter by transaction type and also by date:

 

danielgnic_1-1678460636064.png

This example shows just transactions that are "REGISTER". I'd like to the the same but with dates (for example, I'd like to see just data between march 10th and march 12th).

 

Finally, I'd like to get the frequence that I have per list or product combo, for example:

danielgnic_2-1678460749438.png

 

Consider as well than I'm trying to filter from the beginning customers that bought product "A" and some other product. 

 

Thank you in advance 🙂

 

Hi, @danielgnic 

 

You can try the following methods.
Column:

Product List = 
CONCATENATEX (
    CALCULATETABLE ( VALUES ( 'Table'[Product] ),
        FILTER ( ALL ( 'Table' ),
            [Transaction Type] = EARLIER ( 'Table'[Transaction Type] )
                && [Customer ID] = EARLIER ( 'Table'[Customer ID] ) ) ),
    [Product],
    ","
)

vzhangti_0-1679017380558.png
Measure:

FREQENCE = CALCULATE(DISTINCTCOUNT('Table'[Customer ID]))

vzhangti_1-1679017506691.png

Is this the result you expect?

 

Best Regards,

Community Support Team _Charlotte

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

v-zhangti
Community Support
Community Support

Hi, @danielgnic 

 

You can try the following methods.

New Table = 
SUMMARIZE ( 'Table','Table'[Customer ID],
    "Product Combo", CONCATENATEX ( VALUES ( 'Table'[Product] ), [Product], "," )
)

vzhangti_0-1678433138267.png

Can you provide more sample data to explain the logic of the last graph?

 

Best Regards,

Community Support Team _Charlotte

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

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.