Using ALL on multiple report level filters from the same table

[DAX_merchant]

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:

1. date >= 1/1/2021
2. 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:

1. days in october =
   CALCULATE(COUNTROWS(datedim), datedim[YM] = "202510")
2. days in october (all(currentmonth)) =
   CALCULATE(COUNTROWS(datedim), datedim[YM] = "202510", ALL(datedim[Current Month]))
3. 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. 

[v-hashadapu]

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.

[Amar_Kumar]

@DAX_merchant 

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:

1. A filter on Date[Current Month]

2. 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.

[DAX_merchant]

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? 

[amitchandak]

@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 

[DAX_merchant]

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?

[v-hashadapu]

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.

[DAX_merchant]

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?

[v-hashadapu · Accepted solution]

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.

[DAX_merchant]

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:

1. Date[Date] > 1/1/2021
2. 12/1/2025 <= Date[Date] <= 12/31/2025
3. Current Month = 1

This is why we need to do ALL on both Current Month and Date. 

[d_m_LNK]

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.

[DAX_merchant]

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?

[d_m_LNK]

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.

[DAX_merchant]

Thank you for the response,

There is a sample file linked in the original post, but the Current Month column compares the row level month year to the month year of today. It therefore references Date[Date], as Amar pointed out. My reply to his comment still stands though. 

Regarding Removefilters, I was under the impression that ALL and REMOVEFILTERS were the same function, at least within CALCULATE. REMOVEFILTERS just won't return a table like ALL would, but they are effectively the same function. Could be wrong here though. 

Lastly, I know I could just do ALL(datedim) but, outside of this specific hypothetical, there are situations where I would want to maintain other filters on the table in question, and only remove filters from one column. 

Again, I just want to understand the behavior and am not interested in how to make it work, if that makes sense.