Visual is loading very slowly

[id013]

Hi,

I have a 2 cumulative count visuals, one that shows the cumulative count of units sold by date and the other the cumulative count of unique customers by date. I have a measure that calculates the cumulative units sold # grouped by our 6 sales regions (each color designates a region) and another measure that calculates the cumulative customers also group by our 6 sales regions. The one I'm having problems with is the cumulative customers.

Cumulative Customers 

Both were fine at the begining of the year but I'm find that now the cumulative customers visual is taking a very long time to load the visual. I did a performance trace on it and it was 23000 milliseconds and it confirmed that it was the dax query that calculated the cumulative units sold that was at fault. 

Here is my DAX query:

cnt_cumulative_customers = if(ISBLANK([cnt_customers_all_regions]),CALCULATE([cnt_customers],DATESBETWEEN('Date'[date],MINX(ALL('Date'),'Date'[date]),MAX('Date'[date])-1)),CALCULATE([cnt_customers],DATESBETWEEN('Date'[date],MINX(ALL('Date'),'Date'[date]),MAX('Date'[date]))))

The Dax query for the cumulative units sold is exactly the same EXCEL instead of cnt_customers_all_regions I have another measure which counts the units sold. So I figured out the problem is that the measure I'm using to count customers uses a distinct count where the measure that counts units sold just does a sum. Just for testing purposes I changed the distinct count to a count and the performance improved by over 10000%, 23000 ms to 1000ms. So I guess what I'm asking it is there away for me to still use distinct count without the huge hit in performance? 

[id013 · Accepted solution]

Ok I was able to fix my issue by changing my distinct count measure to use a combination of count rows, summarize & filter instead. The counts seems be correct (at least they match what they did with distinct count) and the performance improvement is the same as when I switched it out to just a count, 23000 milliseconds to just shy of 4000 milliseconds.