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
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
    )
)