Forum Discussion

Jayshamone's avatar
Jayshamone
Helper I
5 years ago
Solved

Basket Analysis

Hello,   I have implemented a basic version of basket analysis which works like this: You select a number of articles from a selection table (Dim Articles). Then you can list all items from anothe...
  • PaulDBrown's avatar
    PaulDBrown
    5 years ago

    Jayshamone 

    Ok, here is one way of doing this. I have kept the model intact, but here are the tables I am using in the solution:

    The slicer on the page all come from the Dim Basket table:

     whereas the fields in the table visuals all come from the Dim Articles table.

    1) To see the values filtered for the Transactions visual, I create a measure to calculate the value based on the Slicers (now basket values tables selection):

     

    Sum of Value (filtered) = CALCULATE([Sum of Value], 
                       USERELATIONSHIP('Fact Positions'[Article Index], 'Dim Basket'[Article Index]))

     

    Sum of Value is a simple SUM('Fact Positions'[Value])

    This measure is to be used in the "Transactions" table to see the value of the items selected in the slicer

    2) To see the items bought together with the articles selected for the basket analysis in the Basket Analysis table visual we need:

     

    Basket Filter =
    VAR ArticleTrans =
        CALCULATETABLE (
            VALUES ( 'Fact Positions'[Transaction ID] ),
            USERELATIONSHIP ( 'Dim Basket'[Article Index], 'Fact Positions'[Article Index] )
        )
    RETURN
        COUNTROWS ( ArticleTrans )

     

    This measure firstly creates a virtual table of unique transaction values which contain the items selected in the Basket Slicers. It then counts the rows.

    This measure is to be used in the filter pane for the table visual. Add the Transaction ID field to the pane, select TopN, add this [Basket Filter] measure and set the value to 1. Basically we are now filtering the visual to only show the Items in the model which are included in the transactions selected from the slicers. 

    3) To filter out the selected values from the basket slicers from the Basket Analysis table visual, we need:

     

    Exclude Basket = 
    VAR BasketV = VALUES('Dim Basket'[Article Index])
    VAR ArticlesV = VALUES('Dim Articles'[Article Index])
    RETURN
    COUNTROWS(EXCEPT(ArticlesV, BasketV))

     

    This measure creates a virutal table of arcticles excluding items selected in the  the basket  and then counts the rows. Therfore the items selected in the basket are excluded from the visual.

    Add this measure also to the filter pane and set the value to 1

     

    As for the fields, use the Dim arcticles and a simple sum measure

     

    I've attached the sample PBIX file for your reference