Forum Discussion

jj_0511's avatar
jj_0511
Helper I
3 years ago

How filter argument in CALCULATE interact with outer filter context on different col in same table

Hello:   I have two tables:   Date: sample data: Date Month Year 2023-01-01    Jan 2023 2023-01-02    Jan 2023 ...    ...   Sales: Date Sales 2023-01-01    9 ...
Need Help
Ashish_Mathur's avatar
Ashish_Mathur
Super User
3 years ago

Hi,

Either of these measures works.  If you want to see the other months as well, you may wrap these functions in the COALESCE() function and specify the second argument as 0.

Sales _ = CALCULATE(SUM(Sales[Sales]), KEEPFILTERS('Date'[Date]=DATE(2023,5,1)))
Sales _1 = CALCULATE(SUM(Sales[Sales]), FILTER(values('Date'[Date]),'Date'[Date]=DATE(2023,5,1)))

Hope this helps.

jj_0511's avatar
jj_0511
Helper I
3 years ago

Thanks, but my questions is why the following measure shows the same value for all Year Month:

 

Sales _ =
CALCULATE(
    SUM(Sales[Sales])
    , 'Date'[Date] = DATE(2023,5,1)
    )
 
Outer filter is on 'Date'[Month Year] and inner filter is on 'Date'[Date], so my understanding is that in this case inner filter does not override outer filter since they are on different columns in same table. So I expect the result using the above measure should be blank except  for May 2023.
  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

    If there is a conflict between the inner and outer filter (which is clearly your case), then the filter condition of the calculate function over writes the outer filter condition.  To reverse this, you may either use the KEEPFILTER() or the FILTER() function.

    • jj_0511's avatar
      jj_0511
      Helper I
      3 years ago

      So the filter condition of the calculate function over writes the outer filter condition even when they are on different columns? Is this behavior documented anywhere?

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Super User
        3 years ago

        Yes, that is what it looks like.  Not sure of where this is documented.