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
You could start by using variables for your min and max dates - that way, they're only evaluated once.
Number of Orders =
VAR MinDate = MIN ( Date_table[Date] )
VAR MaxDate = MAX ( Date_table[Date] )
VAR Result =
CALCULATE (
DISTINCTCOUNT ( circuit[id2] ),
FILTER (
circuit,
AND (
circuit[date1] <= MaxDate,
circuit[date1] >= MinDate
)
)
)
RETURN
Result
Best regards,
Martyn
Thanks MartynRamsden ,
but there are 7320 SE Queris as well and Total time is the same (few miliseconds difference).
As I was on one PBI workshop, I was told, that DISTINCTCOUNT with FILTER is the killing combo. DISTINCTCOUNT is call for every row in my table ant there needs to be many callbacks between FE (formula engine) and SE (storage engine).
I try this one:
Number of Orders = VAR maxDatum = MAX ( Date_table[Date] ) VAR minDatum = MIN ( Date_table[Date] ) RETURN COUNTAX ( FILTER ( VALUES ( circuit[id2] ), AND ( max (circuit[date1]) <= maxDatum, min (circuit[date1]) >= minDatum ) ), COUNT(circuit[id2]) )It runs under 100ms, but counts duplicate values - id2 😞
- MartynRamsden
Hi,
I create one pbix file with some data from my table - I can't share the original table.
hope this will help you.
Thanks
