Forum Discussion

jj_0511's avatar
jj_0511
Icon for Helper I rankHelper 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
Icon for Super User rankSuper 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
Icon for Helper I rankHelper 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.