Forum Discussion
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 |
| 2023-02-01 | 3 |
| 2023-03-01 | 3 |
| 2023-04-01 | 4 |
| 2023-05-01 | 2 |
I have a measure:
Why [Sales_] has same value for all [Month Year] in the table?
Please find pbix here: pbix sample
Thanks.
11 Replies
Hi,
That is because the filter condition of the CALCULATE() function is overriding the implicit filter of the row labels. What result are you expecting?
Ashish_Mathur The result I'm expecting is that there's value for only May 2023 and other rows should be blank since inner and outer filter context match only for May 2023.
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.
Could you expand on "overriding the implicit filter of the row labels" ? where can I find more info on this?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.
Can someone else or someone from Microsoft confirm this: filter condition of the calculate function over writes the outer filter condition even when they are on different columns in same table?
Any experts here can shed some light on this?
just your measure removes all filters
read about it here:
https://www.sqlbi.com/articles/introducing-calculate-in-dax/
https://youtu.be/HxZLkmpY6BA?t=270your measure : CALCULATE( SUM(Sales[Sales]) , 'Date'[Date] = DATE(2023,5,1) ------ its complete syntax CALCULATE( SUM(Sales[Sales]) , FILTER(ALL('Date'), 'Date'[Date] = DATE(2023,5,1))The complete syntax should be:
CALCULATE( SUM(Sales[Sales]) , FILTER(ALL('Date'[Date]), 'Date'[Date] = DATE(2023,5,1))Still calculate filter and outer filter are on different columns in same table.
