Forum Discussion
Solved
Dax query optimization - DISTINCTCOUNT with FILTER
Hi, I need some help. I want to speed up my report. While I was learning DAX, I made some terrible formulas, that I now trying to optimalise them. I have a table with several ID's for one order, on...
Hi lubosst
Does this work for you?
Number of Orders SUMX = VAR MinDate = MIN ( Date_table[Date] ) VAR MaxDate = MAX ( Date_table[Date] ) VAR Result = CALCULATE ( SUMX ( VALUES ( circuit[id2] ), 1 ), circuit[date1] <= MaxDate, circuit[date1] >= MinDate ) RETURN ResultBest regards,
Martyn
MartynRamsden
Solution Sage
Solution SageHi lubosst
Does this work for you?
Number of Orders SUMX =
VAR MinDate = MIN ( Date_table[Date] )
VAR MaxDate = MAX ( Date_table[Date] )
VAR Result =
CALCULATE (
SUMX ( VALUES ( circuit[id2] ), 1 ),
circuit[date1] <= MaxDate,
circuit[date1] >= MinDate
)
RETURN Result
Best regards,
Martyn
Thanks a lot MartynRamsden . That code does the magic 🙂
In DaxStudio I got this result:
From 23sec to 23ms 🙂 Perfect.
- MartynRamsden
Hi lubosst
You're welcome!
Alberto Ferrari wrote a white paper about optimising DISTINCTCOUNT. It's from 2014 but is still relevant and is worth a read.
You can find it here: www.sqlbi.com/whitepapers/understanding-distinct-count-in-dax-query-plans/
Best regards,
Martyn