cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

## Dax formula help needed with cross-sales

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.

`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.

Cheers,

Niels

1 ACCEPTED SOLUTION
Solution Supplier

2 REPLIES 2
Solution Supplier

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.