Nesting basket analysis

[masplin]

Hi

I came across this piece of code for calculating how many of a 2nd product are bought with a first product. PLU is the code for the 1st product and Filter-PLU the same codes for the 2nd product

CALCULATE (
DISTINCTCOUNT(Transactions[TransID]),
CALCULATETABLE (
SUMMARIZE ( Transactions,Transactions[TransID] ),
ALL ( PLU ),
USERELATIONSHIP ( Transactions[PLU], Filter_PLU[Filter PLU] )
)
)

This works perfectly. I've now been asked about baskets of 3 products so select product 1, select product 2 and see what number of transaction have product 3 in them as well. So I'm guessing there is a way to nest the above calculation by creating 3 rd list of codes Filter2_PLU, creating an inactive relationship between Filter_PLU and Filter2_PLU.

Since i pinched the code it isn't obvious to me how i nest it as clearly have create some table using the Filer and Filter2 to feed into the top calculation. If any one smart than me has a suggestion that would be much appreciated.

Mike

 

[Ozeroth]

Hi Mike,

I think it's as simple as adding a second CALCULATETABLE SetFilter argument to the CALCULATE function, rather than nesting.
The two CALCULATETABLEs give you the intersection of PLU/FilterPLU and PLU/Filter2PLU which will be transactions that have all three products.

The structure should be something like this:

=
CALCULATE (
    DISTINCTCOUNT ( Transactions[TransID] ),
    CALCULATETABLE (
        SUMMARIZE ( Transactions, Transactions[TransID] ),
        ALL ( PLU ),
        USERELATIONSHIP ( Transactions[PLU], Filter_PLU[Filter PLU] )
    ),
[COLOR=#ff0000]    CALCULATETABLE ([/COLOR]
[COLOR=#ff0000]        SUMMARIZE ( Transactions, Transactions[TransID] ),[/COLOR]
[COLOR=#ff0000]        ALL ( PLU ),[/COLOR]
[COLOR=#ff0000]        USERELATIONSHIP ( Transactions[PLU], Filter2_PLU[Filter2 PLU] )[/COLOR]
[COLOR=#ff0000]    )[/COLOR]
)

 

[Ozeroth]

There is another approach in this post (that doesn't require copies of the product table):
Turning “OR” Slicers Into “AND” Slicers - PowerPivotPro

 

[Ozeroth]

Well, after experimenting, I think your nesting idea is the way to go after all!

So here is a revised measure that nests one CALCULATETABLE within another.
This appears to execute about twice as fast as my first answer (using DAX Studio with some dummy data).

=
CALCULATE (
    DISTINCTCOUNT ( Transactions[TransID] ),
    CALCULATETABLE (
        SUMMARIZE ( Transactions, Transactions[TransID] ),
[B][COLOR=#ff0000]        CALCULATETABLE ([/COLOR][/B]
[B][COLOR=#ff0000]            SUMMARIZE ( Transactions, Transactions[TransID] ),[/COLOR][/B]
[B][COLOR=#ff0000]            ALL ( Filter_PLU ),[/COLOR][/B]
[B][COLOR=#ff0000]            ALL ( PLU ),[/COLOR][/B]
[B][COLOR=#ff0000]            USERELATIONSHIP ( Sales[Product], Filter2_PLU[Filter2 PLU] )[/COLOR][/B]
[B][COLOR=#ff0000]        ),[/COLOR][/B]
        ALL ( PLU ),
        USERELATIONSHIP ( Sales[Product], FilterProduct[Filter PLU] )
    )
)

The reason this version is faster (as far as I can tell) is that it filters the list of TransIDs with the inner CALCULATETABLE, then further filters the result with the outer CALCULATETABLE.

My original version ran two independent CALCULATETABLEs, each of which filtered all TransIDs before taking the intersection.

It seems that the inner CALCULATETABLE has to include both ALL( Filter_PLU ) and ALL( PLU ) to work. Would be interested in the technical reason why the inner ALL( PLU ) is needed...

 

[Ozeroth]

Slight correction...the ALL(FILTER_PLU) is not needed in the inner CALCULATETABLE.
My mistake - SetFilter arguments don't influence each other and the inner CALCULATETABLE is just another SetFilter of the outer CALCULATETABLE.

=
CALCULATE (
    DISTINCTCOUNT ( Transactions[TransID] ),
    CALCULATETABLE (
        SUMMARIZE ( Transactions, Transactions[TransID] ),
[COLOR=#ff0000][B]        CALCULATETABLE ([/B][/COLOR]
[COLOR=#ff0000][B]            SUMMARIZE ( Transactions, Transactions[TransID] ),[/B][/COLOR]
[COLOR=#ff0000][B]            ALL ( PLU ),[/B][/COLOR]
[COLOR=#ff0000][B]            USERELATIONSHIP ( Sales[Product], Filter2_PLU[Filter2 PLU] )[/B][/COLOR]
[COLOR=#ff0000][B]        ),[/B][/COLOR]
        ALL ( PLU ),
        USERELATIONSHIP ( Sales[Product], Filter_PLU[Filter PLU] )
    )
)

 

[masplin]

I think I should be paying you for solving all my problems!!!

i'll give this a whirl and check the other post

Thanks
Mike