Find Items frequently purchased together

[Anonymous]

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!

[amitchandak · Accepted solution]

@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/

[Jihwan_Kim · Accepted solution]

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

[CNENFRNL]

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.

[Jihwan_Kim · Accepted solution]

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

[Anonymous · Accepted solution]

@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!

[amitchandak · Accepted solution]

@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/

[Anonymous]

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