Forum Discussion
CALCULATE and FILTER behavior
Anonymous,
This is an interesting topic. See the link below for a detailed explanation.
https://www.sqlbi.com/articles/context-transition-and-expanded-tables/
"There are few golden rules in the DAX world, but one for sure is: never filter a table when you can filter a column."
Although this article alone didn't give me the "aha moment" yet (especially the last case, where columns "disappear" when using FILTER), but gave me a good starting point on where I should be digging further 🙂
I probably just need to sit down and go through the process step by step to have a better understanding on how DAX/FILTER/CALCULATE really work behind the scenes 🙂
Thanks!
Super UserAnonymous,
In the second visual (Count Refunded), the columns Delivered and Payment Error have the value 1 because CALCULATE overrides the filter context of status[status], and returns the value for Refunded. If you want to exclude Delivered and Payment Error, you can use KEEPFILTERS:
Count Refunded =
CALCULATE(
[Count],
KEEPFILTERS ( 'status'[status] = "Refunded") )
This will keep the filter for Delivered and Payment Error resulting in blank (a row can't have a status of both Delivered and Refunded). The result is the same as the third visual:
Keep in mind that a column filter and table filter will return the same result in certain scenarios, but not all scenarios. Thus, it's best to follow the aforementioned golden rule. 🙂