Forum Discussion
Basket analysis, trying to set a variable with multiple item combinations
I'm doing a basket analysis and have 7 columns containing 7 items:
Is there a way to search for a specific item combination - say avacado, crackers, hummus, peroni - occuring in a transaction and set this combination as a variable? I'd then like to set a second variable as one of the seven items in column 6 in order to calculate the Support. I hope this makes sense.
You first need to structure your data to look like this.
You can then use a measure like this in a table visual to get the result shown.
ContainsList =
VAR itemstofind = { "avocado", "crackers", "hummus", "peroni" }
VAR thisIDitems =
DISTINCT ( Transactions[Value] )
RETURN
IF ( COUNTROWS ( INTERSECT ( itemstofind, thisIDitems ) ) = 4, "Y", "N" )Pat
5 Replies
- mahoneypat
Microsoft Employee
You first need to structure your data to look like this.
You can then use a measure like this in a table visual to get the result shown.
ContainsList =
VAR itemstofind = { "avocado", "crackers", "hummus", "peroni" }
VAR thisIDitems =
DISTINCT ( Transactions[Value] )
RETURN
IF ( COUNTROWS ( INTERSECT ( itemstofind, thisIDitems ) ) = 4, "Y", "N" )Pat
- lbendlin
Super User
Yes, you can use the DAX function INTERSECT to do this kind of analysis. Before that you will want to bring your source data into a usable format, likely by unpivoting.
If you like more help please provide sanitized sample data in usable format (not as a picture - inserting it into a table would be good).
Maybe this will work? Only the first 50 transactions (out of 1000) are included due to character limits in this post.
AttributeValue Transaction ID 1 Item 1 tuna Item 2 prosciutto Item 3 olives Item 4 ham Item 5 turkey Item 6 sarsaparilla Item 7 gelato Transaction ID 2 Item 1 risotto Item 2 soda Item 3 tuna Item 4 crackers Item 5 peroni Item 6 olives Item 7 prosciutto Transaction ID 3 Item 1 avocado Item 2 crackers Item 3 hummus Item 4 peroni Item 5 ham Item 6 turkey Item 7 pretzels Transaction ID 4 Item 1 olives Item 2 sarsaparilla Item 3 coke Item 4 turkey Item 5 gelato Item 6 ham Item 7 peppers Transaction ID 5 Item 1 tuna Item 2 prosciutto Item 3 apples Item 4 olives Item 5 steak Item 6 avocado Item 7 turkey Transaction ID 6 Item 1 pretzels Item 2 peroni Item 3 chicken Item 4 coke Item 5 gelato Item 6 peppers Item 7 ham Transaction ID 7 Item 1 olives Item 2 sarsaparilla Item 3 coke Item 4 turkey Item 5 gelato Item 6 peroni Item 7 apples Transaction ID 8 Item 1 prosciutto Item 2 peppers Item 3 sarsaparilla Item 4 crackers Item 5 chicken Item 6 gelato Item 7 risotto Transaction ID 9 Item 1 soda Item 2 olives Item 3 sarsaparilla Item 4 crackers Item 5 peroni Item 6 peppers Item 7 risotto Transaction ID 10 Item 1 prosciutto Item 2 peppers Item 3 sarsaparilla Item 4 crackers Item 5 chicken Item 6 moscato Item 7 tuna Transaction ID 11 Item 1 risotto Item 2 pretzels Item 3 apples Item 4 peppers Item 5 avocado Item 6 steak Item 7 turkey Transaction ID 12 Item 1 risotto Item 2 tuna Item 3 avocado Item 4 hummus Item 5 peroni Item 6 apples Item 7 prosciutto Transaction ID 13 Item 1 tuna Item 2 prosciutto Item 3 apples Item 4 olives Item 5 steak Item 6 pretzels Item 7 peroni Transaction ID 14 Item 1 risotto Item 2 pretzels Item 3 apples Item 4 peppers Item 5 avocado Item 6 steak Item 7 gelato Transaction ID 15 Item 1 tuna Item 2 prosciutto Item 3 olives Item 4 ham Item 5 turkey Item 6 coke Item 7 apples Transaction ID 16 Item 1 olives Item 2 sarsaparilla Item 3 coke Item 4 turkey Item 5 gelato Item 6 hummus Item 7 ham Transaction ID 17 Item 1 risotto Item 2 tuna Item 3 avocado Item 4 hummus Item 5 peroni Item 6 coke Item 7 turkey Transaction ID 18 Item 1 pretzels Item 2 peroni Item 3 chicken Item 4 coke Item 5 gelato Item 6 prosciutto Item 7 apples Transaction ID 19 Item 1 soda Item 2 olives Item 3 sarsaparilla Item 4 crackers Item 5 peroni Item 6 risotto Item 7 peppers Transaction ID 20 Item 1 soda Item 2 olives Item 3 sarsaparilla Item 4 crackers Item 5 peroni Item 6 steak Item 7 tuna Transaction ID 21 Item 1 tuna Item 2 prosciutto Item 3 olives Item 4 ham Item 5 turkey Item 6 apples Item 7 risotto Transaction ID 22 Item 1 risotto Item 2 tuna Item 3 avocado Item 4 hummus Item 5 peroni Item 6 sarsaparilla Item 7 prosciutto Transaction ID 23 Item 1 soda Item 2 olives Item 3 sarsaparilla Item 4 crackers Item 5 peroni Item 6 hummus Item 7 steak Transaction ID 24 Item 1 soda Item 2 olives Item 3 sarsaparilla Item 4 crackers Item 5 peroni Item 6 prosciutto Item 7 tuna Transaction ID 25 Item 1 pretzels Item 2 peroni Item 3 chicken Item 4 coke Item 5 gelato Item 6 apples Item 7 risotto Transaction ID 26 Item 1 pretzels Item 2 peroni Item 3 chicken Item 4 coke Item 5 gelato Item 6 prosciutto Item 7 peppers Transaction ID 27 Item 1 olives Item 2 sarsaparilla Item 3 coke Item 4 turkey Item 5 gelato Item 6 apples Item 7 risotto Transaction ID 28 Item 1 olives Item 2 sarsaparilla Item 3 coke Item 4 turkey Item 5 gelato Item 6 soda Item 7 avocado Transaction ID 29 Item 1 soda Item 2 olives Item 3 sarsaparilla Item 4 crackers Item 5 peroni Item 6 hummus Item 7 peppers Transaction ID 30 Item 1 olives Item 2 sarsaparilla Item 3 coke Item 4 turkey Item 5 gelato Item 6 pretzels Item 7 soda Transaction ID 31 Item 1 olives Item 2 sarsaparilla Item 3 coke Item 4 turkey Item 5 gelato Item 6 soda Item 7 ham Transaction ID 32 Item 1 prosciutto Item 2 peppers Item 3 sarsaparilla Item 4 crackers Item 5 chicken Item 6 tuna Item 7 coke Transaction ID 33 Item 1 risotto Item 2 pretzels Item 3 apples Item 4 peppers Item 5 avocado Item 6 prosciutto Item 7 chicken Transaction ID 34 Item 1 soda Item 2 olives Item 3 sarsaparilla Item 4 crackers Item 5 peroni Item 6 moscato Item 7 ham Transaction ID 35 Item 1 risotto Item 2 tuna Item 3 avocado Item 4 hummus Item 5 peroni Item 6 moscato Item 7 soda Transaction ID 36 Item 1 tuna Item 2 prosciutto Item 3 olives Item 4 ham Item 5 turkey Item 6 crackers Item 7 risotto Transaction ID 37 Item 1 avocado Item 2 crackers Item 3 hummus Item 4 peroni Item 5 ham Item 6 prosciutto Item 7 pretzels Transaction ID 38 Item 1 soda Item 2 olives Item 3 sarsaparilla Item 4 crackers Item 5 peroni Item 6 apples Item 7 steak Transaction ID 39 Item 1 risotto Item 2 tuna Item 3 avocado Item 4 hummus Item 5 peroni Item 6 soda Item 7 ham Transaction ID 40 Item 1 pretzels Item 2 peroni Item 3 chicken Item 4 coke Item 5 gelato Item 6 hummus Item 7 steak Transaction ID 41 Item 1 soda Item 2 olives Item 3 sarsaparilla Item 4 crackers Item 5 peroni Item 6 hummus Item 7 coke Transaction ID 42 Item 1 tuna Item 2 prosciutto Item 3 apples Item 4 olives Item 5 steak Item 6 risotto Item 7 chicken Transaction ID 43 Item 1 pretzels Item 2 peroni Item 3 chicken Item 4 coke Item 5 gelato Item 6 crackers Item 7 steak Transaction ID 44 Item 1 soda Item 2 olives Item 3 sarsaparilla Item 4 crackers Item 5 peroni Item 6 steak Item 7 tuna Transaction ID 45 Item 1 tuna Item 2 prosciutto Item 3 olives Item 4 ham Item 5 turkey Item 6 sarsaparilla Item 7 peroni Transaction ID 46 Item 1 prosciutto Item 2 peppers Item 3 sarsaparilla Item 4 crackers Item 5 chicken Item 6 gelato Item 7 gelato Transaction ID 47 Item 1 risotto Item 2 pretzels Item 3 apples Item 4 peppers Item 5 avocado Item 6 chicken Item 7 steak Transaction ID 48 Item 1 risotto Item 2 soda Item 3 tuna Item 4 crackers Item 5 peroni Item 6 pretzels Item 7 hummus Transaction ID 49 Item 1 prosciutto Item 2 peppers Item 3 sarsaparilla Item 4 crackers Item 5 chicken Item 6 turkey Item 7 gelato Transaction ID 50 Item 1 avocado Item 2 crackers Item 3 hummus Item 4 peroni Item 5 ham Item 6 pretzels Item 7 tuna
