Revealing paired products. Calculating how many times of each product was sold with selected product

Azizbek — Mon, 26 Apr 2021 08:31:59 GMT

Hello,

I am working in retail food chain, and I faced with the issue of revealing paired products, like: sausage and bread, cookies and milk, and etc. In other words, revealing products that are mostly sold with another product.

Below, the data structure sample that I have in Power BI:

Purchase_ID	Product_Name
1	Milk
1	Cookie
1	Bread
2	Cookie
3	Bread
3	Cookie
4	Milk
4	Cookie
5	Cookie
5	Bread
5	Sausage
6	Milk
6	Cookie

The result that I would like to reach in the dashboard view is below table:

If selected Product_Name Milk, the result would be below table:

Product name	Number of times sold with selected product
Milk	3
Cookie	3
Bread	1
Sausage	0

Milk was sold with Milk 3 times in Purchuase_IDs: 1, 4 and 6;
Cookie was sold with Milk 2 times in Purchuase_IDs: 1 and 4;
Bread was sold with Milk 1 time in Purchuase_IDs: 1;
Sausage was not sold with Milk in the same Purchuase_ID, that's why is 0.

I was able to do it in EXCEL, but in Power BI I have not reached the result. The excel steps were:
1. Filter Product_Name column by MILK, and reveal values of Purchuase_ID that include MILK;
2. Remove all filters, add filter to Purchuase_ID column with values that I get in 1 step.
3. Using Count function to reveal number of times of each product that has been sold with MILK.

I look forward for your support.
Any help is appreciated.

Re: Revealing paired products. Calculating how many times of each product was sold with selected product

Azizbek — Mon, 26 Apr 2021 10:29:03 GMT

amitchandra thank for your reply, It helped a lot, and I solved it.

topic Re: Revealing paired products. Calculating how many times of each product was sold with selected product in DAX Commands and Tips

Revealing paired products. Calculating how many times of each product was sold with selected product

Re: Revealing paired products. Calculating how many times of each product was sold with selected product

Re: Revealing paired products. Calculating how many times of each product was sold with selected product