KEEPFILTERS with calculate and date

[Zx2000]

Hello,

The problem is pretty complex for me and I can't find answers anywhere.  The question covers calculate algorithm order(which I think I understoodd at this point) + KEEPFILTERS + the most important part, which is a main case of this problem => an invisible ALL('dates') which is added by DAX automatically in the background. The simple question at the end is why the following  formula works in a way it shouldn't (according to my knowledge? is it a bug?)

the formula is: 

Keepfilters and dates problem =
COUNTROWS (
    CALCULATETABLE (
        VALUES ( 'Order Date'[Order Date] ),
        KEEPFILTERS (
            'Order Date'[Order Date]
                < DATE ( 2008, 1, 2 )
        )
    )
)

The  result is:

Let me explain: The outer filter context is [month Year] but the whole DAX adds ALL['Dates'] to the CALCULATE (CALCULATETABLE) in the background (this is really important). Ok, so : 

1.The second argument :

'Order Date'[Order Date]< DATE ( 2008, 1, 2 )
is evaluated in outer filter context but there is (FILTER(ALL(Date[order date])) so the result are just dates <2.2.2008. KEEPFILTERS is not being used here, it will be used to combine this result with new filter context in the end. 

2. The modifier of CALCULATE added by DAX in the background- ALL('date') removes outer filter context from whole date table (it was [month year]) - so there are NO FILTERS in the new context. FIlter Context should be empty!

3.  The filter context as a result of step 1 is added/ combined with a use of KEEPFILTERS to and empty filter context from step 2. 

KEEPFILTERS should have absolutely no inluence in this example on final context as is combines filter from step one with empty filter context. The result should show same values in every row of the matrix but it doeasn't. It acts like it kept outer filter context, 

I tested many things. This general way of thinking works in every other table or with date table connected with datekey instead of date (not mark as a datetable). So it must be a problem with this invisiable ALL('Date') added by DAX. But even if I took this into consideration, as explained in this post, the result should be different. 

I would really appreciate a deep explanation from a user who really knows what is going on 🙂 ...or is it a bug?

[OwenAuger]

Hi there @Zx2000 

Here is my take on an explanation. 

1. Filter arguments provided to the CALCULATE/CALCULATETABLE function are always evaluated in the "outer filter context".
2. When a Filter argument is modified by wrapping it in KEEPFILTERS, it "will not replace any existing filter but will be merged in AND with any previous filters".
   (quoting the linked SQLBI article. "Previous filters" here is the same as the "outer filter context").
3. When the invisible ALL ( 'Order Date' ) is added within CALCULATE/CALCULATETABLE, it is not part of the outer filter context, so it does not affect the evaluation of any of the Filter arguments. It is effectively an additional Filter argument added alongside the explicit Filter arguments.
4. So to explain the behaviour in the measure Keepfilters and dates problem:
   1. The expression 'Order Date'[Order Date] < DATE ( 2008, 1, 2 ) is evaluated in the outer filter context, and indeed contains all dates before DATE ( 2008, 1, 2 ) that exist in the 'Order Date' table.
   2. Wrapping this expression in KEEPFILTERS then modifies the filter by intersecting it with existing filters, specifically the Order Year Month filter. Taking the first row of your table visual as an example, the resulting Filter can be described as:
      Order Date < DATE(2008,1,2) AND Order Year Month = "January 2007"
   3. The invisible ALL ( 'Order Date' ) is added as an additional Filter argument, independent of the above Filter argument.

If we wrote out the DAX expression for the measure for January 2007, it would look like:

Keepfilters and dates problem (January 2007) =
COUNTROWS (
    CALCULATETABLE (
        VALUES ( 'Order Date'[Order Date] ),
        AND (
            'Order Date'[Order Date] < DATE ( 2008, 1, 2 ),
            'Order Date'[Order Year Month] = "January 2007"
        ),
        ALL ( 'Order Date' )
    )
)

In this example, the ALL ( 'Order Date' ) makes no difference to the result, since the first Filter argument provides a more specific filter on the 'Order Date' table. By using KEEPFILTERS, any existing (outer) filters on 'Order Date' or any other tables would be intersected with the explicit Order Date filter.

Well that's my take on it. The SQLBI articles on KEEPFILTERS (and The Definitive Guide to DAX, though that was written a bit earlier) are helpful in this area as well.

Regards,