Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
coachbart
Frequent Visitor

DAX formula using filters

During an online course a measure is made like:

Expenses = CALCULATE(
[Transaction Amount];
FILTER(transactions ; transactions[Type] = "debit" ) ;
Category[Category] <> "transfer" ;
Category[Category] <> "Credit Card Payment"
)

 

What is the difference if a measure is made like

 

Expenses 2 = CALCULATE(
[Transaction Amount];
transactions[Type] = "debit" ;
Category[Category] <> "transfer" ;
Category[Category] <> "Credit Card Payment"
)

 

It result in the same but i don't understand the use of Filter function in the first measure.

 

thnx,

 

Bart

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

@colacan @coachbart  Hey Mate .

  1. 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.

  2. 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 . 

View solution in original post

colacan
Resolver II
Resolver II

@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.

View solution in original post

5 REPLIES 5
colacan
Resolver II
Resolver II

@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.

Anonymous
Not applicable

@coachbart .
Hey Mate ,

You can refer below ,

https://www.sqlbi.com/articles/filter-arguments-in-calculate/ .

 

 

  • When filter expressions are provided, the CALCULATE function modifies the filter context to evaluate the expression. For each filter expression, there are two possible standard outcomes when the filter expression is not wrapped in the KEEPFILTERS function:

    • If the columns (or tables) aren't in the filter context, then new filters will be added to the filter context to evaluate the expression.
    • If the columns (or tables) are already in the filter context, the existing filters will be overwritten by the new filters to evaluate the CALCULATE expression.
  • The CALCULATE function used without filters achieves a specific requirement. It transitions row context to filter context. It's required when an expression (not a model measure) that summarizes model data needs to be evaluated in row context. This scenario can happen in a calculated column formula or when an expression in an iterator function is evaluated. Note that when a model measure is used in row context, context transition is automatic.

@Anonymous  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.

Anonymous
Not applicable

@colacan @coachbart  Hey Mate .

  1. 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.

  2. 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 . 

colacan
Resolver II
Resolver II

@coachbart Hi , one difference I know that, <FILTER(transactions ; transactions[Type] = "debit" )>  filters/iterates whole 'transactions' table, while <transactions[Type] = "debit"> interate only [Type] column. hence the later is better in terms of perfomance.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors