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.
Even though you only filter on [Current Month], Power BI automatically translates that filter into an equivalent filter on the date column.
Because [Current Month] is a calculated column that depends on the date, the storage engine pushes a predicate on Date[Date] into the query plan to make the filter efficient.
So the filter on [Current Month] becomes internally:
Date[Date] >= 12/1/2025
AND
Date[Date] <= 12/31/2025
That means the filter context contains two filters:
A filter on Date[Current Month]
A hidden filter on Date[Date] (generated automatically)
When you write:
CALCULATE(..., ALL(Date[Current Month]))
You only remove the first filter.
But the hidden date-range filter remains, so October 2025 is still excluded.
That’s why this returns the wrong result.
Only the 3rd measure works:
CALCULATE(..., ALL(Date[Current Month]), ALL(Date[Date]))
Because it removes both filters that the engine applied.
Power BI does this because the engine optimizes filtering by pushing filters down to the base column involved in calculating [Current Month]
Since [Current Month] is derived from Date[Date], the engine rewrites the filter in SQL as a date range—this is faster and more efficient.
This is called a filter propagation / predicate pushdown optimization.
Amar,
Thank you for the response. I had figured this was the case, but why then, when I remove the report level filter on Date[Date] >= 1/1/2021, does the second measure work?
If what you say is true, then having ONLY Current Month filtered to 1 would require both ALL statements right?
DAX_merchant , Based on what I got filter on the page is actually the current month. To remove that, you need to remove the filter on the current column or the date table. As you have used a filter on date as well current month , you need remove both for filter to work
That is why this works
CALCULATE(COUNTROWS(datedim), datedim[YM] = "202510", ALL(datedim[Current Month]), ALL(datedim[Date]))or you can try
CALCULATE(COUNTROWS(datedim), datedim[YM] = "202510", ALL(datedim[Date]))Ideally
CALCULATE(COUNTROWS(datedim), datedim[YM] = "202510")
This means
CALCULATE(COUNTROWS(datedim), filter(all(datedim[YM] ), datedim[YM] = "202510"))but not removing the filter from other columns
Thank you for the response.
Are you saying that despite the filter on Date[date] NOT affecting the measure, it needs to be removed anyways because it exists in the filter pane?
I can accept this as being fact, but it is very confusing why this would be the case.Again, the only filter affecting the context of the calculation would be the current month filter. I also know that if ONLY the current month filter exists in the filter pane, and not the date[date] filter, I can use ALL(current month) to get the expeted result. However, if currenth month AND date[date] (despite date[date] NOT affecting the calculation) are both in the filter pane, I MUST use ALL on date[date] as well because date[date] is listed in the filter pane?
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.
