Common elements, two tables

[capko]

Hello,

I have two tables with three columns each: article reference, client and price. The first table correspond to the first year of the shop and the second one to the second year.

I would like to perform an analysis using these two tables. This analysis should be able to be performed as follows:

• References bought by all clients on the first year
• References bought by all clients on the second year
• References bought by all clients both years

I will need to use a slicer in order to select 'Year 1', 'Year 2' or both at the same time and I will also add one another slice that should be allow me to select between 'Common/Not common' elements. I will also need to add a filter on the price (select only elements with price > x$).

The problem I'm facing is how to identify common elements. I cannot use an intersect because the common elements between the two tables will be depending on the client and reference...

Any idea on the approach for this problem ?

[danextian]

Hi @capko ,

Looks like your use case is a candidate for basket analysis. There are a lot of blog posts out there tht tackles this topic and youtube videos as well. Each has a different approach but they do have common elements

https://blog.finance-bi.com/power-bi-basket-analysis/

https://www.daxpatterns.com/basket-analysis/

https://towardsdatascience.com/explore-the-potential-of-products-through-customers-purchase-behaviou...

https://businessintelligist.com/2021/03/08/market-basket-analysis-power-bi-dax-tutorial/

If none of those works, you might want to post a sample data as it is hard for anyone (not necessarily me) who wants to help to just use his/her imaginaton without any data to work on. In the past, I would post a link to an excel file in a cloud storage with formula/logic for my use case and have others convert it to Power BI logic.

On a side note, here's how you can get better/faster response to your post:

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/td-p/1447523/jump...