Help Understanding Calculate

[brennahurley]

I need help understanding when one should use the filter function within calculate. I have the two following DAX formulas:

(a) 

var notTwoToThree = if(isblank(CALCULATE(

        COUNTROWS(FactScoreChange),

        FILTER(

            FactScoreChange,

            FactScoreChange[Name] = personName &&

            FactScoreChange[Year Range] = "2022-2023"

        )

    )),TRUE,FALSE)

(b)

var notTwoToThree = if(isblank(CALCULATE(

        COUNTROWS(FactScoreChange),

            FactScoreChange[Name] = personName,

            FactScoreChange[Year Range] = "2022-2023"

    )),TRUE,FALSE)

To me, it seems like they should accomplish the same thing, but they produce different results. Can someone help me understand why?

[v-tsaipranay]

Hi @brennahurley ,

We haven’t received an update from you in some time. Could you please let us know if the issue has been resolved?
If you still require support, please let us know, we are happy to assist you.

Thank you.

[v-tsaipranay]

Hi @brennahurley ,

Thanks for reaching out to the Microsoft fabric community forum. Also thank you @danextian , @Ashish_Mathur and @lbendlin  for your helpful response.

I wanted to check if you had the opportunity to review the information provided . If you still require support, please let us know, we are happy to assist you.

Thank you.

[danextian · Accepted solution]

Hi @brennahurley 

Your first measure will return a value for the row where in both of these conditions are satisfied: FactScoreChange[Name] = personName and FactScoreChange[Year Range] = "2022-2023". It  will return blank for everything else. It guarantees the user selection is preserved. Your second measure will return the filtered value for all rows of Year Range and Name

Which one to choose depends on your use case. The second one can be used to compare a specific filtered value vs all other values for different rows -  for example Aparel and EMEA vs Apparel and APAC. As a best practice though, filtering a table is to be avoided and filtering columns instead. You can use KEEPFILTERS  on column to preserve the user selection instead of FILTER. The perfomance difference between the two is neglible on small tables.

[Ashish_Mathur]

Hi,

You do not need to use a FILTER() function here.  A FILTER() is used when you have to compare a measure to a measure, column to a column or column to a measure.  Since you are comparing a column to a fixed value, the FILTER() function is not required.

[lbendlin]

They are not doing the same thing.

if(isblank(CALCULATE(

        COUNTROWS(FactScoreChange),

        FILTER(

            FactScoreChange,

            FactScoreChange[Name] = personName &&

            FactScoreChange[Year Range] = "2022-2023"

        )

    )),TRUE,FALSE)

is equivalent to 

if(isblank(CALCULATE(

        COUNTROWS(FactScoreChange),

        FILTER(

            ALLSELECTED(FactScoreChange),

            [Name] = personName ),

FILTER(

            ALLSELECTED(FactScoreChange),

[Year Range] = "2022-2023"

        )

    )),TRUE,FALSE)