topic Re: Find Items frequently purchased together in DAX Commands and Tips

Find Items frequently purchased together

Anonymous — Mon, 17 Oct 2022 19:24:41 GMT

I have a sales invoice table that lists items purchased. What I want to do is create a table of item pairs to determine the items that are most often purchased together. Here's an example table:

Invoice	Item
S1	A
S1	B
S1	C
S1	D
S1	E
S2	A
S2	B
S2	E
S2	F
S3	B
S4	A
S4	B
S4	C
S4	D
S4	E
S4	F
S4	G
S4	H
S5	C
S5	F
S5	H

In this case, the item pair A,B occurs 3 times, B,E occurs 2 times, etc.

I'm not sure how to do this without looping.

Any help is very much appreciated!

Re: Find Items frequently purchased together

amitchandak — Tue, 18 Oct 2022 02:59:05 GMT

Anonymous , to me it seem like a market basket problem

https://finance-bi.com/power-bi-basket-analysis/
https://businessintelligist.com/2017/03/27/market-basket-analysis-using-power-bi/
https://www.daxpatterns.com/usecases/market-basket-analysis/

Re: Find Items frequently purchased together

Jihwan_Kim — Tue, 18 Oct 2022 03:07:15 GMT

Hi,

I am not sure how your desired output looks like, but please check the below picture and the attached pbix file.

Invoice count measure: = VAR _invoicelistitemA = SUMMARIZE ( CALCULATETABLE ( Data, Data[Item] = SELECTEDVALUE ( Item_A[Item] ), ALL ( Item_B[Item] ) ), Data[Invoice] ) VAR _invoicelistitemB = SUMMARIZE ( CALCULATETABLE ( Data, Data[Item] = SELECTEDVALUE ( Item_B[Item] ), ALL ( Item_A[Item] ) ), Data[Invoice] ) RETURN IF ( SELECTEDVALUE ( Item_A[Item] ) = SELECTEDVALUE ( Item_B[Item] ), BLANK (), COUNTROWS ( INTERSECT ( _invoicelistitemA, _invoicelistitemB ) ) )

Re: Find Items frequently purchased together

Anonymous — Tue, 18 Oct 2022 03:46:20 GMT

amitchandak It is most definitely a market basket problem, although I forgot the common name.
After submitting this question, I was pointed to a similar post:
https://community.powerbi.com/t5/DAX-Commands-and-Tips/Common-Items/m-p/2002442
It provides me the output I'm looking for.
I will review the links above and compare results.
Thank you very much for your reply.

Re: Find Items frequently purchased together

Anonymous — Tue, 18 Oct 2022 05:36:39 GMT

Jihwan_Kim
The output I want is a table of item pairs and frequency of occurrence. So based on the sample data, the output would look like this:

Pair	Count
A - B	3
A - C	2
A - D	2
A - E	3
A - F	2
A - G	1
A - H	1
B - C	2
B - D	2
B - E	3
B - F	2
B - G	2
B - H	2
C - D	2
C - E	2
C - F	2
C - G	1
C - H	2
D - E	2
D - F	1
D - G	1
D - H	1
E - F	2
E - G	1
E - H	1
F - G	1
F - H	2
G - H	1

That matches the upper or lower diagonal of your matrix. I can convert the format easily enough.
Thanks very much!

Re: Find Items frequently purchased together

CNENFRNL — Tue, 18 Oct 2022 13:56:39 GMT

An interesting brain teaser. Try to wrap your head around the following solution, then essence of DAX, such as Calculate(), relationship, context transition are no more secrets to you.