Forum Discussion
Help Understanding Calculate
I need help understanding when one should use the filter function within calculate. I have the two following DAX formulas:
(a)
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.
5 Replies
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.
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 toif(isblank(CALCULATE(COUNTROWS(FactScoreChange),FILTER(ALLSELECTED(FactScoreChange),[Name] = personName ),FILTER(ALLSELECTED(FactScoreChange),[Year Range] = "2022-2023"))),TRUE,FALSE)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.
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.
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.
