Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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."
Proud to be a 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."
Proud to be a Super User!
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!
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. 🙂
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |