Forum Discussion
Using ALL on multiple report level filters from the same table
Hi DAX_merchant , Thank you for reaching out to the Microsoft Community Forum.
In DAX, rows are never evaluated first and then filtered, the engine always does the opposite. The filter context defines the complete rowset up front and the calculation is evaluated only inside that rowset. If a row is inside the constrained set, it is considered, if it is outside, it does not exist to the calculation at all. There is no later step where DAX asks whether an existing filter is actually affecting the result.
A filter on a calculated column like [Current Month] does not permanently or universally become a filter on Date[Date]. That rewrite happens opportunistically. When [Current Month] is the only filter, removing it leaves no remaining constraints, so October becomes visible. When an explicit Date[Date] filter is also present, the engine now has two independent constraints on the same table. Removing only one does not expand the evaluation space, because the other constraint still defines the rowset.
So yes, even if only one filter truly excludes rows in a way that changes the result, all filters that exist must be removed to escape the constrained context. DAX does not rank, simplify or ignore filters based on perceived impact. Filters define the evaluation space and that space must be fully cleared before the engine can see beyond it.
Hi DAX_merchant , Thank you for reaching out to the Microsoft Community Forum.
DAX does not reason about whether a filter is logically relevant to the result, it only cares whether a filter exists in the filter context. If a filter exists on a table, it constrains the rows the engine is allowed to scan, even if that filter does not exclude the value you are trying to calculate. There is no concept of a harmless or redundant filter in DAX.
In your case, there are two filters on the same table. One is explicit (Date[Date] >= 1/1/2021). The other is [Current Month] = 1, which is a calculated column derived from Date[Date]. When [Current Month] is filtered, the engine internally translates that into a date range predicate on Date[Date] for efficiency. When you use ALL(Current Month), you only remove the visible calculated column filter. The remaining Date[Date] filter whether it came from the report filter or from predicate pushdown still limits the evaluation space, so October 2025 never becomes visible.
This also explains the behavior change when you remove the report level Date[Date] filter. With that filter gone, removing ALL(Current Month) leaves no remaining constraints on the table, so the measure works as you expect. ALL(column) only removes filters on that column, not other filters on the same table. If multiple filters exist, even if they appear redundant, they must all be explicitly removed for the engine to evaluate outside that constrained rowset.
Thank you amitchandak , d_m_LNK and Amar_Kumar for your valuable responses.
Thank you,
Below, I have copied your response with some comments / questions.
------------------------------------------------
DAX does not reason about whether a filter is logically relevant to the result, it only cares whether a filter exists in the filter context. If a filter exists on a table, it constrains the rows the engine is allowed to scan, even if that filter does not exclude the value you are trying to calculate. There is no concept of a harmless or redundant filter in DAX. This feels illogical, especially given the rest of the response, but I can understand it conceptuatlly. I understand the fact that it constrains rows, but if my row is within the set of constrained rows, the calculation should not be affected. This is what I had previously believed to be the premise of DAX.
In your case, there are two filters on the same table. One is explicit (Date[Date] >= 1/1/2021). The other is [Current Month] = 1, which is a calculated column derived from Date[Date]. When [Current Month] is filtered, the engine internally translates that into a date range predicate on Date[Date] for efficiency. Is this always the case? If it would always operate this way behind the scenes, then by definition, having the filter only on current month would require ALL(Date[Date]). When you use ALL(Current Month), you only remove the visible calculated column filter. The remaining Date[Date] filter whether it came from the report filter or from predicate pushdown still limits the evaluation space, so October 2025 never becomes visible.
This also explains the behavior change when you remove the report level Date[Date] filter. With that filter gone, removing ALL(Current Month) leaves no remaining constraints on the table, so the measure works as you expect. Based on the above, this means that when [Current Month] is the sole filter in the filter pane, the filter is not translated to Date[Date]; is this assumption correct? To bulid on this, if we have both filters (current month and date[date] >1/1/2021), you said that the current month filter get's translated to date[date]. The way I understand this is that now, we have two filters on date[date] (date[date] > 1/1/2021 AND 12/1/2025 <= Date[date] <= 12/31/2025) shouldn't this mean that we can use ALL(date[date]) and get the desired result? I know this doens't work which is why I am confused. ALL(column) only removes filters on that column, not other filters on the same table. If multiple filters exist, even if they appear redundant, they must all be explicitly removed for the engine to evaluate outside that constrained rowset. This is the most important part for me to understand; what you are saying is that even though only ONE filter is actually constraining rows in a manner that would affect my calculation, all other filters must be removed, so there are no longer any constraints, no matter if they affect my calculation or not?
Hi DAX_merchant , Thank you for reaching out to the Microsoft Community Forum.
In DAX, rows are never evaluated first and then filtered, the engine always does the opposite. The filter context defines the complete rowset up front and the calculation is evaluated only inside that rowset. If a row is inside the constrained set, it is considered, if it is outside, it does not exist to the calculation at all. There is no later step where DAX asks whether an existing filter is actually affecting the result.
A filter on a calculated column like [Current Month] does not permanently or universally become a filter on Date[Date]. That rewrite happens opportunistically. When [Current Month] is the only filter, removing it leaves no remaining constraints, so October becomes visible. When an explicit Date[Date] filter is also present, the engine now has two independent constraints on the same table. Removing only one does not expand the evaluation space, because the other constraint still defines the rowset.
So yes, even if only one filter truly excludes rows in a way that changes the result, all filters that exist must be removed to escape the constrained context. DAX does not rank, simplify or ignore filters based on perceived impact. Filters define the evaluation space and that space must be fully cleared before the engine can see beyond it.
Ok,
Based on what you have said, the filters will translate "opportunistically". I can accept this even though it would be nice to know what opportunities you are referencing.Just to reiterate:
When there is a filter on Current month AND Date[Date] we have 3 applied constraints:- Date[Date] > 1/1/2021
- 12/1/2025 <= Date[Date] <= 12/31/2025
- Current Month = 1
This is why we need to do ALL on both Current Month and Date.