Is using Filter inside the Calculate makes any difference?

[sathishsam]

I have a DAX function written in 2 ways, i see that the 2nd one performs better always, then my question is where should i use the first once

1) Calculate ( SUM(TableA[Col1], TableA[Col2] = "Some Filter", 
FILTER(TableB, TableB[Col1] = "Some Filter") )

2) Calculate ( SUM(TableA[Col1], TableA[Col2] = "Some Filter", TableB[Col1] = "Some Filter") 

I am still learning DAX and its fun. Thanks for the help in advance

[v-xuxinyi-msft]

Hi @sathishsam 

The first parameter of the FILTER function is the table, and the second parameter is the filter condition, for example:

My sample:

Measure:

Measure = CALCULATE(COUNT('Date'[Date]), FILTER('Date', [Date] >= DATE(2023, 2, 22) && [Date] <= TODAY()))

Result:

My formula filters out dates in the range of February 22, 2023 to today to calculate how many dates there are in total.

For more details about Funtions I used, you can read related document link: 

FILTER function (DAX) - DAX | Microsoft Learn

COUNT function (DAX) - DAX | Microsoft Learn

CALCULATE function (DAX) - DAX | Microsoft Learn

Best Regards,
Community Support Team _Yuliax

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sathishsam]

Thank you for the reply. How do i write an optimized DAX in this case, the first one usually takes 5 to 6 times more than the second one.

[talespin]

Hi @sathishsam,

Calculate ( SUM(TableA[Col1], TableA[Col2] = "Some Filter", TableB[Col1] = "Some Filter") 

Inside a calculate this filter (TableB[Col1] = "Some Filter") translates to 

FILTER( ALL(TableB[Col1]), TableB[Col1] = "Some Filter") 

ALL removes any filter on Col1 and creates a new filter.

FILTER(TableB, TableB[Col1] = "Some Filter")

This is different

If there is any filter coming from outside, TableB will contains rows corresponding to that filter and that will intersect with filter "Some Filter".

In DAX Filters are tables.