Forum Discussion

robarivas's avatar
robarivas
Post Patron
4 years ago

DISTINCTCOUNT Optimization

I believe the DistinctOrders DAX measure below gives accurate results and is faster than other versions I've tried but I need to know if I can make it faster. With the model I'm working with (see bel...
distinctcount
Need Help DAX measure
optimization
daXtreme's avatar
daXtreme
Solution Sage
4 years ago

Hi robarivas 

 

You are calling a measure in an iterator and on top of that you're iterating the whole column regardless of what's already been selected/crossfiltered in it.

 

KEEPFILTERS ( FILTER ( ALL ( 'SalesFact'[ClientID] ), [OrderTransactionQuantity by Order Date] <> 0 ) )

 

This line makes your measure slow.

 

By the way, why is there KEEPFILTERS in there? What's the reason? Are you by any chance slicing in the UI by the field SalesFact[ClientID]? That would be pretty bad as well but KEEPFILTERS in this place does nothing to make your measure faster.

  • robarivas's avatar
    robarivas
    Post Patron
    4 years ago

    Thank you daXtreme Is there an alternative formulation you think might fix this? The pattern I used is one I got from page 703 of the Definitive Guide to DAX (2nd edition). Instances where a ClientID's quantity nets to zero on an Order Date need to not be considered in the DIstinct Count. If the data was more aggregated (so that I wouldn't have to sum the quantity) then that might help avoid referencing a measure maybe. 

    • daXtreme's avatar
      daXtreme
      Solution Sage
      4 years ago

      Try this:

       

      OrderTransactionQuantity by Order Date =
CALCULATE(
    SUM( 'SalesFact'[OrderTransactionQuantity] ),
    USERELATIONSHIP ( 'SalesFact'[Order Date], 'Calendar'[Date] )
)

DistinctOrders =
COUNTROWS(
    FILTER(
        DISTINCT( 'SalesFact'[ClientID] ),
        // Edit: Had to change > to <> as I can see from your data
        // you can have negative quantities as well.
        [OrderTransactionQuantity by Order Date] <> 0
    )
)

       

      • robarivas's avatar
        robarivas
        Post Patron
        4 years ago

        Thanks again daXtreme I did a quick check and it looks like your formula also may be producing accurate results. However, initial testing suggests it may not be any faster. So I will do some more robust testing but I'm starting to think any/all remaining speed issues may be solely attributable to model structure/size/ design rather than unoptimized DAX.