Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 transacti...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hello,

     

    Yes, I'd like more help.

    I unpivoted the columns and saved as .pbix file but I'm not sure how to include it in a post. 

  • mahoneypat's avatar
    mahoneypat
    4 years ago

    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

     

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
4 years ago

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

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Pat,

     

    This worked - thank you so much!

     

    Sarah