Forum Discussion
Using ALL on multiple report level filters from the same table
Hello Forum,
I want to preface this post by aknowledging that the situation described is purely hypothetical and I know that a situation like this would likely never arise in an actual report. I am not looking for an answer like "just don't do it that way", I am trying to understand why this behavior is occuring to deepen my understanding of the DAX engine.
I have a report with a single table, a date table (1/1/2020 - 1/1/2026). This date table contains a couple columns (pbix can be found here) with the most important being a "current month" column that flags all dates in the current month with a 1, others with a 0.
There are two report level filters:
- date >= 1/1/2021
- current month = 1
I am aware that these filters are redundant. Current month will return 12/1/2025 - 12/31/2025, so we don't need the filter on date[date].
I have 3 measures:
- days in october =
CALCULATE(COUNTROWS(datedim), datedim[YM] = "202510") - days in october (all(currentmonth)) =
CALCULATE(COUNTROWS(datedim), datedim[YM] = "202510", ALL(datedim[Current Month])) - days in october (all(currentmonth) & all(datedim(Date))) =
CALCULATE(COUNTROWS(datedim), datedim[YM] = "202510", ALL(datedim[Current Month]), ALL(datedim[Date]))
Only the third one shows the correct value of 31.
The first measure will obviously not work.
However, I don't understand why the second measure doesn't work. The only filter restricting dates to not include october 2025 is the filter on current month. So why then do I need to remove filters from Date[Date] to get this measure to work?
To my understanding, filtering on current month doesn't filter date[date] on the backend. I have assumed this because when I remove the report level filter on date[date] measure 2 does in fact work.
Any insight would be greatly appreciated.
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.
13 Replies
You need the ALL Date because your current month filter is limiting the filter context the DAX can evaluate on. By adding the filter of the ALL(Datedim[date]), you are including all the dates from that table where previously you were only evaluating that dax in against the current filter context. You could also use the removefilters(datedim[date]) to expand the context of your measure.
I would imagine that ALL(Current Month) would achieve that result. That is the only filter that would actually affect the measure since the report level filter on date[date] is essentially irrelevant here.
Why specifically do I need to use ALL(Date[date]) when that filter doesn't affect the calculation?You don't actually need to specify the column for the ALL function. You could just say ALL(Datedim) and it would work without specifying each column. How does your Current Date column work? I am guessing it has something to do with if that is truly only returning the current date the ALL(Date[Date]) is not only removing the context but also doing the calculation over all rows of that column. Using remove filters might be more accurate and not require both columns. Then you could just specify: removefilters(datedim[CurrentMonth]) and that may work as well.
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/2025That 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
HI DAX_merchant , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.
