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
Solution 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.

  • pauln150's avatar
    pauln150
    New Member
    7 years ago

    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's avatar
      AkhilAshok
      Solution Sage
      7 years ago

      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.