Forum Discussion
DAX formula using filters
Using FILTER has significant performance impact, which can be clearly seen looking at query plans and utilization of Storage Engine vs Formula Engine. It creates additional temporary table that it needs to "interact" with already existing filters coming from report/pivot table itself (rows, columns, slicers). You won't notice anything for simple average value in single cell, but if your [x] measure itself is complicated and there are many of those "initial" filters, the difference in calculation time can be huge.
FILTER retains and iteracts with initial filter context, while filter expression used directly in CALCULATE ignores it. See what happens, when I add ReadDate to the pivot table:
This is precisely why the measure without FILTER is faster: it doesn't care what dates are in columns - it already calculated one "true" value, while the measure with FILTER evaluates itself against initial filters for every row.
Results in both columns can be considered correct - it really all depends on interpretation and how you name the measures ;).
As a general rule I would suggest you don't use FILTER when you don't have to. Save it's power for when it's really needed.
You Guys Can refer this document on stack overflow and try 1 comment SQLDI Link .It is also good to read
filter https://stackoverflow.com/questions/50506030/dax-calculate-function-with-and-without-filter
Thank You .
coachbart Hi coachbart,
To the point,
Expenses 2 = CALCULATE([Transaction Amount]; transactions[Type] = "debit" )
is just sytext sugaring of
Expenses 2 = CALCULATE([Transaction Amount],
Filter (ALL('transactions[Type]),
'transactions[Type] = "debit" )
)above two systexes are equivalent, and there are no performance differences between them.
now if you comapre Express 2 and Express (which uses filter(transactions) ), only one deference is
Expenses 2 = ... ... Filter (ALL('transactions[Type]), .... this one itereates only [Type] column
Expenses = ... ... Filter (transactions, .... this one itereates whole 'transactions' table
hence Expenses 2 is better in terms of performance.
Resolver IIAnonymous Hi Harish,
If wee see the 2 codes above, both are filter arguments of Calculate. how the filter modifier and context transintion would be different between the two? I would appreciate if you explain the differenct.
Using FILTER has significant performance impact, which can be clearly seen looking at query plans and utilization of Storage Engine vs Formula Engine. It creates additional temporary table that it needs to "interact" with already existing filters coming from report/pivot table itself (rows, columns, slicers). You won't notice anything for simple average value in single cell, but if your [x] measure itself is complicated and there are many of those "initial" filters, the difference in calculation time can be huge.
FILTER retains and iteracts with initial filter context, while filter expression used directly in CALCULATE ignores it. See what happens, when I add ReadDate to the pivot table:
This is precisely why the measure without FILTER is faster: it doesn't care what dates are in columns - it already calculated one "true" value, while the measure with FILTER evaluates itself against initial filters for every row.
Results in both columns can be considered correct - it really all depends on interpretation and how you name the measures ;).
As a general rule I would suggest you don't use FILTER when you don't have to. Save it's power for when it's really needed.
You Guys Can refer this document on stack overflow and try 1 comment SQLDI Link .It is also good to read
filter https://stackoverflow.com/questions/50506030/dax-calculate-function-with-and-without-filter
Thank You .