How to optimize DAX

[pauln150]

I am trying to build a report for Market Basket Anaylsis.  I am developing with 10 weeks of data, about 6 million sales transcations and 6700 unique items.  

I am using this DAX to determine if an item is in sold with another item in the same sales transaction.  

Baskets with both items =
    CALCULATE(
        DISTINCTCOUNT(Sales[BasketKey]),
            SUMMARIZE(Sales, Sales[BasketKey]),
            ALL('Item'),
             USERELATIONSHIP(Sales[RetailItemKey], 'Item (Filtered)'[RetailItemKey])
             )

The calculation seems to take about 8 - 10 seconds everytime a visulization is filtered or changed make the report unusable and I would like to run it against multiple years of data, about (60 million records).  Any suggestions would be appreciated.  

Thanks

[AkhilAshok]

How about this:

Baskets with both items =
VAR BothItemsBasket =
    CALCULATETABLE (
        SUMMARIZE ( Sales, Sales[BasketKey] ),
        ALL ( 'Item' ),
        USERELATIONSHIP ( Sales[RetailItemKey], 'Item (Filtered)'[RetailItemKey] )
    )
RETURN
    CALCULATE ( DISTINCTCOUNT(Sales[BasketKey]), BothItemsBasket )

If the number of unique BasketKeys are small, then query should be fast.

[pauln150]

Thanks for the suggestion, unfortunately , same performance.  The typical basket has 2 items so I expect about 3 million baskets for a 10 week period.

[AkhilAshok]

Have you tried checking the Storage Engine queries which are getting triggered when a visual is executed (you can get this in DAX Studio or SQL Profiler)? It should ideally execute 2 queries, one for getting the unique baskets for filtered Item, and next for computing the distinct count of baskets filtered by the unique baskets from first query. So if the list of unique items gets big (in the range of millions) the query can get slow. But, you should check it out to confirm.