Basket Analysis

[Jayshamone]

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 another table (dim basket) with the quantity and value of the articles which are bought together with the selected articles from Dim Articles.

This works mostly fine. However right now its not very memory efficient, plus I am not able to not output the selected articles.

The basic model looks like this:

The DAX for the basket Calculation looks like this:

Sum Value Basket =
CALCULATE(
    sum('Fact Positions'[Value]),
    CROSSFILTER('Dim Articles'[Article Index], 'Fact Positions'[Article Index], None),
    CROSSFILTER('Fact Basket'[Transaction ID], 'Fact Transactions'[ID], Both),
    USERELATIONSHIP('Dim Articles'[Article Index], 'Fact Basket'[Article Index]),
    USERELATIONSHIP('Fact Basket'[Transaction ID], 'Fact Transactions'[ID]),
    USERELATIONSHIP('Fact Positions'[Article Index], 'Dim Basket'[Article Index])
)

How can I adjust the basic model or DAX to filter the selected articles?
Is there any DAX that allows you to connect the [Dim Basket] directy to the Table [Fact Positions]

The other posts I found seem to have a different basic model or other use cases so that it's hard to just copy those solutions. 

Thank you in advance
Justus

[PaulDBrown]

@Jayshamone 

You should alter your model to include Dim Tables for common "Id" fields (for example Transactions ID). This will make the filtering direct (instead of using CROSSFILTER etc).
For example, the expression CROSSFILTER('Dim Articles'[Article Index], 'Fact Positions'[Article Index], None), seems redundant since you already have a relationship between these two tables. 

What is the structure of the visual you are using btw?

It would help if you could share a sample PBIX file, "hiding" (change the field names) any confidential data.

[Jayshamone]

Hello,

What do you mean by including Dim Tables for common ID fields? That's what I have. However the only actual dimension in the sample are the articles. Other dimensions in the main model are date, shop, till, customers, ...

With Crossfilter (id1, id2, none) I try to lose the relationship between two tables, since I do not need it for this measure - it even breaks it. Any other combination of one-directional relationships also lead to wrong results (especially totals).

The main goal is to find out which other articles (undefined) are bought together with the selected articles.

For example: Customers who buy red wine, also buy ...???
This works - but the result mostly shows me that people who buy red wine also buy red wine.... which is not that helpful - so I'm trying to exclude the selected articles.

I was somehow unable to include a file in the post - so here is the the download link.

Thank you for your help.
Justus

[PaulDBrown · Accepted solution]

@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

[Jayshamone]

I'm still struggling a bit -

Is there any way to include the filters (Exclude Basket, Top 1 Transactions by Basket Value) into the measures?

[Jayshamone]

I was able to include the filters like this:

Sum Value Basket = 

CALCULATE(

 sum('Fact Positions'[Value]),

 USERELATIONSHIP('Dim Basket'[Article Index], 'Fact Positions'[Article Index]),

 REMOVEFILTERS('Dim Articles'),

 CALCULATETABLE(VALUES('Fact Positions'[Transaction ID])),

 EXCEPT(VALUES('Dim Basket'[Article Index]), VALUES('Dim Articles'[Article Index]))

)

[PaulDBrown]

@Jayshamone 

Great you found a way!

Here is another option:

1) Create a measure to identify the transactions involving both the Items and Basket:

Filter Art Index =
VAR Basket =
    CALCULATETABLE (
        VALUES ( 'Fact Positions'[Transaction ID] ),
        REMOVEFILTERS ( 'Fact Positions' ),
        USERELATIONSHIP ( 'Dim Basket'[Article Index], 'Fact Positions'[Article Index] )
    )
VAR Articles =
    VALUES ( 'Fact Positions'[Transaction ID] )
RETURN
    COUNTROWS ( INTERSECT ( Articles, basket ) )

2) the final measure to calculate the values:

Final Value = 
CALCULATE([Sum of Value], FILTER('Fact Positions', [Filter Art Index] = 1))

 to exclude the selected values from the table, you can still use the [Exclude basket] in the filter pane, or include it as another filter expression in the final measure (though I tend to use the filter pane to keep measures as simple as possible -you can always hide these filters in reader mode to avoid possible tempering by users)

[Jayshamone]

Hey,

the result seems to be exactly what I'm hoping for. Thank you.

However I need to be able to write the DAX so that the users can use the measures without explicitly filtering the top 1 ...

I'm trying to implement this approach into our model and will accept your solution as soon as it works 🙂

[amitchandak]

@Jayshamone ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Can you let us know, what are you trying to do here? two cross join, there should be some reason behind it