Common Items

[SammyNed]

Hi guys,

Can someone please assist? 

I have a table of data, (a simplified view is below), but basically we sell products.

There are 3 columns:

Order ID (unique per order)

Item_Name (items that can be added in an order)

Item_Qty (number of item_names that can be added in an order)

I want to know which items are frequently bought together , can use this info to perhaps have combo deals. A sample of my outcome is shown below:

Please assist...

Thanks in advance

[CNENFRNL]

PBI = PQ + DAX. Make the best of PQ; a mere transformation makes it way much easier,

let
    Source = List.Distinct(SALES[Item_Name]),
    Combination = Table.FromRows(List.TransformMany(Source, each List.Skip(Source, List.PositionOf(Source, _)+1), (x,y) => {x,y}), {"Item1","Item2"})
in
    Combination

[SammyNed]

Hi @CNENFRNL 

Thanks for your assistance. Your solution works...

However I currently have over 3100 items for sale in the table so unfortunatly it creates a massive table that bombs out on my laptop wants it reaches about 86million rows. 

I'm not too farmilar with PQ, but I was thinking perhaps we could add a conditional formula in, something like only list the items purchased over 3 times together?

Currently the Sales Table just contains a list of items bought by date, so we could summarize the Sales table by count of unique items and then apply a filter for only items bought more than 3 times and then apply your suggestion above?

Would that be possible?

Thanks in advance

[Greg_Deckler]

@SammyNed Here is a cleaned up version:

Table 2 = 
    VAR __Table = 
        DISTINCT(
            FILTER(
                GENERATE(
                    SELECTCOLUMNS(DISTINCT('Table'[Item_Name]),"Item_Name_1",[Item_Name]),
                    SELECTCOLUMNS(DISTINCT('Table'[Item_Name]),"Item_Name_2",[Item_Name])
                ),
                [Item_Name_1]<>[Item_Name_2])
            )
    VAR __Table2 = 
        ADDCOLUMNS(
            __Table,
            "__var",
            VAR __t1 = SUMMARIZE(FILTER('Table',[Item_Name] = [Item_Name_1] || [Item_Name] = [Item_Name_2]),[Order_ID],"__Count",COUNTROWS('Table'))
            RETURN
            COUNTROWS(FILTER(__t1,[__Count]>1))+0
        )
RETURN
    __Table2

[SammyNed]

Hi @Greg_Deckler 

Thanks foryour assistance.

However i currently have over 3100 items in the table and my laptop bombs out as 3100 factorial is undefined. 

[Jihwan_Kim]

Orders Count Basket : =

VAR _itemoneorders =

VALUES ( Sales[Order_ID] )

VAR _itemtwoorders =

CALCULATETABLE (

VALUES ( Sales[Order_ID] ),

ALL ( 'Item One' ),

TREATAS ( VALUES ( 'Item Two'[Item_Name_2] ), Sales[Item_Name] )

)

VAR _newtable =

INTERSECT ( _itemoneorders, _itemtwoorders )

RETURN

IF (

HASONEVALUE ( 'Item One'[Item_Name_1] )

&& HASONEVALUE ( 'Item Two'[Item_Name_2] ),

IF (

SELECTEDVALUE ( 'Item One'[Item_Name_1] )

<> SELECTEDVALUE ( 'Item Two'[Item_Name_2] ),

COUNTROWS ( _newtable )

)

)

Link to the sample pbix file 

[Greg_Deckler]

@SammyNed Here is one method:

Table 2 = 
    VAR __Table = GENERATE(SELECTCOLUMNS(DISTINCT('Table2'[Item_Name]),"Item_Name_1",[Item_Name]),SELECTCOLUMNS(DISTINCT('Table2'[Item_Name]),"Item_Name_2",[Item_Name]))
    VAR __Table1 = SELECTCOLUMNS(SUMMARIZE('Table2','Table2'[Order_ID],"__Items",CONCATENATEX('Table2',[Item_Name],",")),"__Items",[__Items])
    VAR __Table2 = 
        ADDCOLUMNS(
            __Table,
            "__var",
            VAR __t1 = SUMMARIZE(FILTER('Table2',[Item_Name] = [Item_Name_1] || [Item_Name] = [Item_Name_2]),[Order_ID],"__Count",COUNTROWS('Table2'))
            RETURN
            COUNTROWS(FILTER(__t1,[__Count]>1))
        )
RETURN
    __Table2

In this example, 'Table2' is the source data table you presented.