Top product combinations sold - DAX measure

[michaelsh]

I need to create a Measure that shows which Two-Product Combinations are most common.

For example, here A,B is most common, because 3 customers have it.

Although D is the most common product with 4 customers, but I don't want to see it in my measure, because it is sold alone, not as a combination.

So I need my measure to return "A,B"

Thanks!

Customer	Product
111	A
111	B
111	C
222	X
222	Y
222	Z
333	D
444	D
555	D
666	D
777	A
777	B
888	A
888	B
999	X
999	Y

[mahoneypat · Accepted solution]

Basket analysis is probably the best way (didn't confirm), but here is another approach.  Put this measure in a card to get "A,B" as the result.

Highest Combo =
VAR cj1 =
    DISTINCT ( SELECTCOLUMNS ( Products, "Product1", Products[Product] ) )
VAR cj2 =
    DISTINCT ( SELECTCOLUMNS ( Products, "Product2", Products[Product] ) )
VAR cj =
    FILTER ( CROSSJOIN ( cj1, cj2 ), [Product1] <> [Product2] )
VAR summary =
    ADDCOLUMNS (
        cj,
        "@total",
            VAR prod1 = [Product1]
            VAR prod2 = [Product2]
            RETURN
                COUNTROWS (
                    FILTER (
                        ALL ( Products[Customer] ),
                        AND (
                            NOT ( ISBLANK ( CALCULATE ( COUNTROWS ( Products ), Products[Product] = prod1 ) ) ),
                            NOT ( ISBLANK ( CALCULATE ( COUNTROWS ( Products ), Products[Product] = prod2 ) ) )
                        )
                    )
                )
    )
VAR productslist =
    CONCATENATEX (
        TOPN ( 1, summary, [@total], DESC, [Product1], ASC ),
        [Product1] & ", " & [Product2]
    )
RETURN
    productslist

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.  With some more effort, i may be able to get A&B but you may take it forward from here.

Hope this helps.

[amitchandak]

@michaelsh , I think this has to do with market basket analysis

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/
https://www.mssqltips.com/sqlservertip/5428/market-basket-analysis-in-r-and-power-bi/

[michaelsh]

@amitchandak 

Yes, this is a Basket Analysis question.

I have researched all these links before I posted. All these topics are similar but they are not answering my case.

I am looking for a specific solution for my case, please.

Thank you

[mahoneypat · Accepted solution]

Basket analysis is probably the best way (didn't confirm), but here is another approach.  Put this measure in a card to get "A,B" as the result.

Highest Combo =
VAR cj1 =
    DISTINCT ( SELECTCOLUMNS ( Products, "Product1", Products[Product] ) )
VAR cj2 =
    DISTINCT ( SELECTCOLUMNS ( Products, "Product2", Products[Product] ) )
VAR cj =
    FILTER ( CROSSJOIN ( cj1, cj2 ), [Product1] <> [Product2] )
VAR summary =
    ADDCOLUMNS (
        cj,
        "@total",
            VAR prod1 = [Product1]
            VAR prod2 = [Product2]
            RETURN
                COUNTROWS (
                    FILTER (
                        ALL ( Products[Customer] ),
                        AND (
                            NOT ( ISBLANK ( CALCULATE ( COUNTROWS ( Products ), Products[Product] = prod1 ) ) ),
                            NOT ( ISBLANK ( CALCULATE ( COUNTROWS ( Products ), Products[Product] = prod2 ) ) )
                        )
                    )
                )
    )
VAR productslist =
    CONCATENATEX (
        TOPN ( 1, summary, [@total], DESC, [Product1], ASC ),
        [Product1] & ", " & [Product2]
    )
RETURN
    productslist

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anita01]

Hi Pat, @mahoneypat 

Do you hve any tips I could do the same but with combination of 3 products? 

Best Regards,
Anita

[Anita01]

Or how can I retrieve the table with eg. top 10 product combos within certain date frame selected on slicer? 
Is that possible?