topic Re: Common Items in DAX Commands and Tips

Common Items

SammyNed — Fri, 06 Aug 2021 12:55:06 GMT

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

Re: Common Items

Greg_Deckler — Fri, 06 Aug 2021 13:51:13 GMT

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.

Re: Common Items

Jihwan_Kim — Fri, 06 Aug 2021 13:56:22 GMT

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

Re: Common Items

Greg_Deckler — Fri, 06 Aug 2021 14:01:44 GMT

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

Re: Common Items

CNENFRNL — Fri, 06 Aug 2021 19:54:48 GMT

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

Re: Common Items

SammyNed — Mon, 09 Aug 2021 16:12:54 GMT

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

Re: Common Items

SammyNed — Mon, 09 Aug 2021 16:15:44 GMT

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.

Re: Common Items

CNENFRNL — Mon, 09 Aug 2021 18:39:31 GMT