Forum Discussion
CALCULATE and FILTER behavior
Hey,
I'm trying to wrap my head around the following:
I have 2 simple tables:
The contents of these 2 tables:
Orders:
| order_id | product | status_id |
| 1 | Bike | 1 |
| 2 | Bike | 1 |
| 3 | Skateboard | 2 |
| 4 | Skateboard | 3 |
| 5 | Laptop | 2 |
status:
| status_id | status |
| 1 | Delivered |
| 2 | Refunded |
| 3 | Payment Error |
I have 3 measures:
Count = COUNTROWS('Orders')Count Refunded =
CALCULATE(
[Count],
'status'[status] = "Refunded")Count Refunded with Filter =
CALCULATE(
[Count],
FILTER(
'status',
'status'[status] = "Refunded"))
And they produce these results:
(The 3 matrices have the same row/column fields: Orders[product] and status[status]).
I understand why I get the results with Count Refunded: Calculate overrides the context, so no matter what I have in columns, it will always be the count of refunded items (Bonus question though: why do "Delivered" and "Payment Error" get the same values as "Refunded"? Shouldn't they just be 0 [or blank]?). In this case, columns stay.
However, when I do kinda the same but with FILTER in CALCULATE, the rest of the columns (Delivered, Payment Error) disappear.
So the question is: what causes this behavior? (I'm probably missing something with the FILTER function but I'm not sure what)
Sample file: calculate and filter.pbix
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."
3 Replies
- DataInsights
Super User
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!
- DataInsights
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. 🙂
