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