Dax formula help needed with cross-sales

[C4YNelis]

Dear all,

I'm trying to show all cross-sales (different products sold on the same order), based on a product selected (preferably more than one, but at this stage, I'm happy to see it work with just one).

I have two tables, one with all unique products (appr. 500k lines) and one with all sales lines (including products) (appr. 2mln lines). See below for an example.

I'd like to use a slicer based on the products table to select a given product, in order to list all other products sold on the same orders. I have tried many different formulas by now and frankly, I'm at a loss as to how best solve this problem.

Example of datastructure and desired outcome

My best guess is that I need a calculated column to begin with showing products or null, whether or not the respective order contains the sliced product. From there on it should be relatively easy to calculate all the other figures.

Ideally, the sliced product is excluded from the matrix, however, to keep things simple, if the sliced product would be part of the matrix, I could live with that.

So far, I have tried different formulas with combinations of calculate(table), summarize, groupby, contains, filter and userelationship, however, nothing provided the desired result. I have tried to disable the relation in my report between the slicer and the matrix, but then I don't succeed in using the slicer at all with my formulas.

My latest try was this, however, it obviously does not work:

Cross-sales products = If(Contains(All('Sales table');'Sales table'[Orderno];if(Contains('Sales table';'Sales table'[product (sold)];[products (all)]);'Sales table'[Orderno];""));'Sales table'[Product (sold)];"")

Please point me in the right direction.

Thank you for your help!

Cheers,

Niels

[sokg · Accepted solution]

The Italians have made good job here   http://www.daxpatterns.com/basket-analysis/

[C4YNelis]

@sokg,

Thank you for the link! It worked like a charm.

I had almost the exact same solution at one point, only I tried to create the table dynamically with DAX and that just didn't work.

Best regards,

Niels