CALCULATE and FILTER behavior

[uadrian92]

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 

[DataInsights · Accepted solution]

@uadrian92,

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."

[uadrian92]

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]

@uadrian92,

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. 🙂