Basket Analysis Matrix

[jak82]

Is there a way to do a basket analysis matrix in powerpivot or an addon that does it for you, our minds are blown.

 

[michaelsmith559]

This link has alot of good projects to do in excel. Here is one for market basket analysis: Market Basket Analysis

 

[jak82]

Thanks Michael, I am trying to replicate the tutorial here

Basket Analysis – DAX Patterns

But it doesn't seem to like my DAX any ideas?

=CALCULATE (
    DISTINCTCOUNT ( AdDetails[adnumbr] ),
    CALCULATETABLE (
        SUMMARIZE ( AdDetails, AdDetails[adnumbr] ),
        ALL ( titles ),
        USERELATIONSHIP ( AdDetails[ttlname], 'FilterTitle'[filter filterttlname])
    )
)

It doesn't seem to like the filter function next to filterttlname

Thanks

C

 

[michaelsmith559]

I had a look at the website you linked and I am not sure. However, I found this link https://msdn.microsoft.com/en-us/library/dn282369.aspx
Microsoft make a shopping basket analysis add-in. It may be of some use if you cannot figure out the other.

 

[Matt Allington]

It doesn't seem to like the filter function next to filterttlname

There is no filter function in your formula. There is a USERELATIONSHIP that is using a relationship override between the AdDetails table and the 'FilterTitle' table. Is that what you intended? Have you created the inactive relationship in the data model?

 

[jak82]

There is no filter function in your formula. There is a USERELATIONSHIP that is using a relationship override between the AdDetails table and the 'FilterTitle' table. Is that what you intended? Have you created the inactive relationship in the data model?

Thanks Guys Michael, that looks like a proof of concept, don't think there is actually a tool yet

Matt, I have created the inactive Link, when I add the filter to the formula

=CALCULATE (
    DISTINCTCOUNT ( AdDetails[adnumbr] ),
    CALCULATETABLE (
        SUMMARIZE ( AdDetails, AdDetails[adnumbr] ),
        ALL ( titles ),
        USERELATIONSHIP ( AdDetails[ttlname], 'FilterTitle'[ filter filterttlname] )
    )
)

I get the error message I thinks filter is part of the column name it doesn't seem to recognise it as a function.

Thanks

 

[Matt Allington]

The word FILTER can be a function, but only if you use it right. The first occurrence of FILTER it is encapsulated in single quotes ie 'FilterTitle'. In this case, it is not a function but a part of the table name. In the second use of the word FILTER you have it encapsulated in square brackets. in this second case it is part of a Column Name that belongs to the table mentioned previously. If you want to use FILTER as a function, then you should not encapsulate it this way and always use the arguments that go with the function

FILTER(TABLE, filter expression)

 

[jak82]

The word FILTER can be a function, but only if you use it right. The first occurrence of FILTER it is encapsulated in single quotes ie 'FilterTitle'. In this case, it is not a function but a part of the table name. In the second use of the word FILTER you have it encapsulated in square brackets. in this second case it is part of a Column Name that belongs to the table mentioned previously. If you want to use FILTER as a function, then you should not encapsulate it this way and always use the arguments that go with the function

FILTER(TABLE, filter expression)

Thanks Matt, got it working