DAX: Collateral effect of EARLIER?

[AlB]

I was working on this DAX puzzle and initially came up with the following measure to solve it. Files here.

Measure 1 :=
AVERAGEX (
    DATESBETWEEN ( 'Date'[Date], FirstDate1, LastDate1 ),
    COUNTROWS (
        CALCULATETABLE (
            Orders,
            Orders[StartDate] <= 'Date'[Date],
            Orders[EndDate] >= 'Date'[Date],
            ALL ( 'Date' )
        )
    )
)

This throws the well-known error "The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression". It can be solved by using FILTER on the highlighted arguments but  what caught my attention is that the following works and does not throw any error:

Measure 2 :=
AVERAGEX (
    DATESBETWEEN ( 'Date'[Date], FirstDate1, LastDate1 ),
    COUNTROWS (
        CALCULATETABLE (
            Orders,
            Orders[StartDate] <= EARLIER('Date'[Date]),
            Orders[EndDate] >= EARLIER('Date'[Date]),
            ALL ( 'Date' )
        )
    )
)

At the time the highlighted section is executed,  'Date'[Date] refers to exactly the same in both the inner and outer row contexts, so EARLIER('Date'[Date]) and 'Date'[Date] should also be the same. Anyone know what's going on? Why does the system not complain with the addition of EARLIER?

Many thanks

[parry2k]

@AlB here are couple of posts which explain the functionality of earlier function and hope it is helpful.

http://tinylizard.com/dax-earlier-function/

https://powerpivotpro.com/2012/03/the-correct-usage-of-earlier/

[AlB]

Thanks @parry2k

I know how EARLIER works. It is the behaviour that I explain in the question what I do not understand. 

[Zubair_Muhammad · Accepted solution]

@AlB

Here are my two cents on this

When  we use a logical expression such as

 table[column] = <value>

It is internally translated into a table expression

  FILTER (

        ALL ( table[column] ),

        table[column] = <value>

    )

)

So when you use

Orders[StartDate] <= 'Date'[Date]

DAX engine cannot determine which table[Column] is to be transformed to ALL(table[column])

So there are two ways to handle this

One is to specify precisely

Filter(all(Orders[StartDate]),Orders[StartDate] <= 'Date'[Date])

Other one is to use EARLIER telling the engine to treat Orders[StartDate] as the table expression on which to iterate

Orders[StartDate] <= earlier('Date'[Date])

[AlB]

Hi @Zubair_Muhammad

Thanks a lot for your reply. Interesting. So if I understand correctly, what you're saying is that without EARLIER the instruction is ambiguous as the engine could actually use any of the two columns to iterate for the filter operation. With the addition of EARLIER, that ambiguity is gone. Correct?

Thanks 

[Zubair_Muhammad]

HI @AlB

Following excerpt is from this book on DAX

Microsoft-Building-Models-PowerPivot-Business

[Zubair_Muhammad]

@AlB

Also EARLIER becomes available for use whenever a NEW ROW CONTEXT is created inside a function. irrespective of the fact whether the earlier row context was on the same table or not

So if you write these 2 calculated columns in date table both work

column = COUNTROWS(Filter(Orders,Orders[EndDate]='Date'[Date]))

column 2 = COUNTROWS(Filter(Orders,Orders[EndDate]=earlier('Date'[Date])))