DAX Function for 'Customers also bought'

[JosephJC]

My Power BI dashboard contains a product filter and I would need to create a Power BI chart to show 'Customers who bought this product also bought the following ...'. Which would be a bar chart of the products by Price.

My data is flattened, consisting of an Orders table with the following: Customer, Product, Price, Qty, Date.

Need help to build the DAX query for achieving this and handle the context correctly. I tried using ALL and FILTER but did not manage to get the correct result as yet.

[OwenAuger · Accepted solution]

Hi @JosephJC

A Basket Analysis pattern will work for this.

http://www.daxpatterns.com/basket-analysis/

Here's a dummy example pbix

First create a 'Filter Product' table with an inactive relationship with your Orders table:

You use the 'Filter Product' table to select the product(s) to be analysed.

Then, assuming you have created your base measures for Quantity/Price etc, you create 'basket analysis' measures with this pattern (taken from DAX Patterns):

Order Quantity for Customers who bought Filter Product = 
CALCULATE (
    [Order Quantity],
    CALCULATETABLE (
        SUMMARIZE ( Orders, Orders[Customer] ),
        ALL ( Orders[Product] ),
        USERELATIONSHIP ( Orders[Product], 'Filter Product'[Filter Product] )
    )
)

This measure will return the Order Quantity for any Products purchased by Customers who purchased the products selected in 'Filter Product'.

Hopefully this or something similar meets your needs.

Cheers,

Owen

[Lesliekeziah]

Hi Owen, 

Thank you so much for this! I have another question please, what formula should I include to sum up the quantity/product if i selected more than 1 product in the slicer.  Thank you.

[OwenAuger]

Hi @Lesliekeziah

With the measure as it is, selecting multiple products on the slicer will return values for Customers that purchased any of the selected products.

Did you want a different behaviour? Could you provide a small example of the result you're wanting?

[Lesliekeziah]

Hi @OwenAuger 

Thank you for the reply.

Right now when i select two products/items in the slicer, it will calculate separately the sum of the other items sold together (as shown below).

The Result I Wanted: If Selected Items are D & E (multiple products sold together to customers) , Data Will Show

D 21

E 2

F 24

G 14

Hope this makes sense. Thank you so much for your help.

[OwenAuger]

Hi again, just got back to looking at this now.

It could be that either the measure or model needs to be adjusted.

What do you get if you remove the grouping by the outer Item in the visual (PivotTable)?

Could you post a simple table of data (similar to that behind your previous post) and expected result, just to confirm the required logic?

[AlexaderMilland]

Hi Owen, I know it's an old post but it was quite helpful.

Is there a way to filter out the product you choose in the filter? I want to see all OTHER products (assuming it should work on multiple selections too)

[OwenAuger]

@AlexaderMilland Glad to hear it 🙂

Just to clarify, since this is a Basket Analysis example, did you want the 'Filter Product' selection to be inverted?

For example, do you want to make the "primary" Product selection on one slicer, then make the 'Filter Product' selection on another slicer, and have a measure that returns Order Quantity for Customers that purchased the "primary" Product selection but not 'Filter Product' selection?

Regards,

Owen

[AlexaderMilland]

I want the filter production selection that i make on the slicer to not show up on table that i return afterwards.
E.g. i want to see all OTHER products in the baskets that contain product X , but not show product X itself.
Right now when i click for example "pants", It might say
Pants : 5 
Socks : 3
Shoes : 2
Hats : 1
I want it to just say
Socks : 3
Shoes : 2
Hats : 1

(Product X is actually going to be a long selection in my model, but should be similar solution)

[Ashish_Mathur]

Hi,

Just for my information, why do you not want to see Pants: 5?  Also, i think the way to depict data is in the form of a matrix visual.

[AlexaderMilland]

Hi Ashish,

2 reasons mainly.

- My actual model is not just a selection of brands, it's a selection of 100 products compared to the remaining 50.000, so it might clutter up.
- It's going to be a comparrison between 100 products and another 100 products, so it should be easier to compare if the selected products themselves do not appear. 

[OwenAuger]

Sure thing 🙂

I have updated my sample PBIX with an example - attached.

In this model, I have also created 'Product' and Customer dimensions to better represent a typical model, and adjusted the measures accordingly.

Here is one of the new measures, that returns Quantity for Customers who bought the selected "Filter Products", but only for products other than the selection:

Order Quantity for Customers who bought Filter Product (excl Filter Product) = 
CALCULATE (
    [Order Quantity],
    CALCULATETABLE (
        SUMMARIZE ( Orders, Customer[Customer] ),
        ALL ( 'Product' ),
        USERELATIONSHIP ( Orders[Product], 'Filter Product'[Filter Product] )
    ),
    KEEPFILTERS (
        EXCEPT (
            VALUES ( 'Product'[Product] ),
            VALUES ( 'Filter Product'[Filter Product] )
        )
    )
)

Regards,

Owen

[WettenVamos]

Hi Owen,

I have a similar problem with subscription. I think your solution will work fine, but i need an extra check. Subscription have a start and an end date, and i want to check if the subscription were active during the same period. 

Can you help me?

[OwenAuger]

Hi @WettenVamos 

Yes, I think I can help with that.

Just to confirm the requirements:

Comparing with the example above, rather than "Products" you have "Subscriptions".

Each Subscription has a start & end date.

Any given customer may have purchased multiple Subscriptions

Then (as an example) you want to be able to see for customers that purchased Subscription A, which other Subscriptions did they purchase, as long as the Subscriptions overlap in time?

Could you show what your tables or data model look like?

Regards,

Owen

[AlexaderMilland]

Works like a charm!