DAX All/VALUES Combination Very Slow

[asocorrocognex]

I am looking for some insight into why a certain measure is very slow.  I am using DAX.do so you may run the query below yourself.  I want a table that displays customers, orders dates, and the count of distinct products purchased by each customer.  But the count needs to be for all time, not just for the date listed.  This is a sample of the end state:

This is the full code:

DEFINE
MEASURE Sales[Distinct Products] =
IF (
    DISTINCTCOUNT ( Sales[ProductKey] ) > 0,
    CALCULATE (
        DISTINCTCOUNT ( Sales[ProductKey] ),
        ALL ( Sales ),
        VALUES ( Customer[CustomerKey] )
    )
)
EVALUATE
SUMMARIZECOLUMNS (
    Customer[CustomerKey],
    'Date'[Date],
    "Distinct Products for All Time", [Distinct Products]
)
ORDER BY Customer[CustomerKey] DESC

Why is this so slow?  I have tried several flavors of counts and CALCULATE modifiers but to no avail.

Thanks!

[selimovd]

Hey @asocorrocognex ,

I think your measure is too complicated for what you want.

If I got it right you want the customers and day and always the DISTINCTCOUNT ( Sales[ProductKey] ) per customer. This would mean your measure just have to ignore all filter on the date. Like this the following measure should do it:

DEFINE
    MEASURE 'Sales'[Distinct Products] =
        CALCULATE ( DISTINCTCOUNT ( Sales[ProductKey] ), ALL ( 'Date'[Date] ) )
EVALUATE
SUMMARIZECOLUMNS (
    Customer[CustomerKey],
    'Date'[Date],
    "Distinct Products for All Time", [Distinct Products]
)
ORDER BY Customer[CustomerKey] DESC

Also the IF for me doesn't make sense as you return BLANK as else value. If there is no row, anyway BLANK will be returned.

If you need any help please let me know.

If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

Best regards

Denis

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[asocorrocognex]

Thanks for your reply.  That indeed works and is fast.  The reason for the IF is to avoid non-sensical combinations resulting out of the cross-join created by default in the Summarize and enabled by the ALL ( Sales ) in the measure (here's a good explanation of that by the sqlbi guys: https://youtu.be/Kb-AqMQzLAk).

How would you generalize for any number of dimensions?  That's the reason for my use of the ALL/VALUES pattern as explained here: https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/.

Cheers!

[jdbuchanan71]

@asocorrocognex 

Not sure why you have the IF statement in there, it is not doing anything other than calculating the distinct count a second time.  Also, assuming you have the customer table linked to the sales table and you really want the count for all time like the column name suggests, give this a try.

Distinct Products =
CALCULATE ( DISTINCTCOUNT ( Sales[ProductKey] ), REMOVEFILTERS ( 'Date' ) )

[asocorrocognex]

Hi, thanks for your reply.  The reason for the IF is to avoid non-sensical combinations of customers and dates.

That is indeed faster.  However, I was looking to follow the ALL/VALUES pattern explained here:

https://www.sqlbi.com/articles/using-allexcept-versus-all-and-values/#

Using ALL ( Sales ) I wanted to get rid of any potential other filters, not just dates, so the measure would be more generic.