cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

## 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

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors