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

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.

  • daXtreme's avatar
    daXtreme
    Solution Sage
    4 years ago

    Hiya

     

    The problem you've got is that you filter rows based on a value of a measure. So, FILTER has to call it for each and every ClientId in the current context. If there are only a few clients, that's OK. But if there are thousands of them... This is where things start to get a bit hairy.

     

    You could still try to code it in a different way. For instance:

    [Distinct Orders] =
COUNTROWS(
    FILTER(
        ADDCOLUMNS(
            DISTINCT( SalesFact[ClientID] ),
            // I can see that quantities can be negative, hence
            // the check must be <> instead of >. The same applies
            // to my previous measure.
            "@QtyIsNonZero", [OrderTransactionQuantity by Order Date] <> 0
        ),
        [@QtyIsNonZero] // this is of type bool, so no comparison is needed
    )
)