Basket analysis - basket with same product category (more than 1)

[gracejoshi]

Hello everyone,

I have seen the usual pattern for Basket analysis (https://www.daxpatterns.com/basket-analysis/). And I am using the similar data model.   

Here is what I want :

When I select "Dishwasher" product category from [Product] table and [Filter Product], Only 1 transaction should qualify as [Basket with Both Products] i.e transation ID "xxx123".  At the moment with my current formula, I am getting 3 transactions intead of 1. Basically, if a basket contains same product more than once, that I want that to qualify and want to ignore other transaction where it exist just once.

 

Here is my formula :

Here is how my result look like:

I did look at https://www.daxpatterns.com/basket-analysis/ post for excluding Same product selection. But that excludes even my "xxx123" transaction. and gives me blank if the same product is selected in Product and Filter Product table.

Would really appreciate any help.

Thanks

[Mariusz]

Hi @gracejoshi 

Try adding the below as an extra argument to your CALCULATETABLE statement.

FILTER( Sales, Sales[ProductCount] > 1 ) 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[gracejoshi]

Hi @Mariusz 

If i apply this condition, i am getting no value for other transactions.

It should be like the following  (except against the fridge category it should show 0)

Here is my transactions for fridges :

I would want to attach my pbix file , dont know how to do that. That would have been useful to understand the scenario.

Thanks

Grace