Forum Discussion

uadrian92's avatar
uadrian92
Frequent Visitor
4 years ago
Solved

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 ...
Need Help
uadrian92's avatar
uadrian92
Frequent Visitor
4 years ago

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's avatar
    DataInsights
    Super User
    4 years ago

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