Forum Discussion

pauln150's avatar
pauln150
New Member
7 years ago

How to optimize DAX

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 a...
Need Help
AkhilAshok's avatar
AkhilAshok
Icon for Solution Sage rankSolution Sage
7 years ago

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.