topic Re: Basket analysis - basket with same product category (more than 1) in DAX Commands and Tips

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

gracejoshi — Tue, 29 Oct 2019 06:01:04 GMT

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

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

Mariusz — Tue, 29 Oct 2019 09:28:42 GMT

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.

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

gracejoshi — Thu, 07 Nov 2019 00:40:28 GMT

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