Cannot use EARLIER() in ADDCOLUMNS()

[markus_zhang]

Hi experts,

I think that ADDCOLUMNS() is equivalent to creating a calculated column right? So here is my case.

Basically I want to add a "JoinedNov" column to indicate whether this user's first login was in November or not, and here is the format of data. Very simple, just two columns:

EMAIL |  logins

a@a.com   |  2018-11-12

a@a.com   |  2019-01-12

So I'd expect this "JoinedNov" will show 1 for both rows. And here is the piepc of code:

VAR
    NumUserNovActive = 
        CALCULATE(
            DISTINCTCOUNT(Login[email]),
            FILTER(
                FILTER(
                    ADDCOLUMNS(
                        Login,
                        "JoinedNov",
                        IF(
                            AND(
                                CALCULATE(
                                    FIRSTNONBLANK(Login[logins], Login[logins] = EARLIER(Login[logins])) >= DATE(2018, 11, 1),
                                    ALL(Login)
                                ),
                                CALCULATE(
                                    FIRSTNONBLANK(Login[logins], Login[logins] = EARLIER(Login[logins])) <= DATE(2018, 11, 30),
                                    ALL(Login)
                                )
                            ),
                            1,
                            0
                        )
                    ),
                    [JoinedNov] = 1
                ),
                LASTNONBLANK(Login[logins], Login[logins] = EARLIER(Login[logins])) >= TODAY() - 28
            )
        )

However I was very suprised to find out that EARLIER refers to an earlier row context which doesn't exist. What's the problem here? Thanks in advance. Please ignore the LASTNONBLANK() row.

BTW I welcome new methods but I need to know what exactly goes wrong in my code.

[AlB]

Hi @markus_zhang

Here's my take.

You're using the two FIRSTNONBLANKs in the expression parameter (first argument) of your CALCULATE. At that point, context transition has occurred and all (previous) row contexts have been converted into filter contexts. The row context from the ADDCOLUMNS that you talk about correctly, is therefore no more. So you only have the row context from the FIRSTNONBLANK( ) and therefore the EARLIER( ) fails.

[markus_zhang]

Hi, @AlB thanks for the explanation. I vaguely felt that CALCULATE() was the source of the problem and pretty glad to learn from you about context conversion. I read a few articles about that, but am more and more concerned about what contexts exactly are, in the Vertipaq engine, do you have any insight on this topic? I'm just curious, but I think understanding the database engine as well as the parsing of DAX is going to help a lot with writing them.

[AlB]

@markus_zhang

You're welcome. You always have interesting, well thought-out questions. 

I'm not sure knowing the internals of the engine would help with that. I'm certainly no expert in it so don't take my word for it. Take into account, though, that in many cases the way operations are carried out at low level differs greatly from what you would expect. The engine is built with performance in mind and without the restriction to bear any resemblance to your code at the higher level. But like I said, do NOT take my word for it. If you see that it can actually help, please do let me know. I'd be interested in hearing about it.

My view is that it should be possible to understand contexts and context transition well without the need to delve into the engine.      

[markus_zhang]

Thanks for the help! Really can't do the job without you guys.

BTW I figured out the code, instead of CALCULATE(FIRSTNONBLANK()) I'm using:

MINX(
    FILTER(
        ALL(Login),
        Login[email] = EARLIER(Login[email])
    ),
    Login[logins]
) >= DATE(2018, 10, 1)

And it seems that the row context is preserved and EARLIER() does work.

[AlB · Accepted solution]

@markus_zhang

Just to elaborate a bit more. On the example table Login as shown in your initial post, let's add a calculated column and see what happens with different pieces of code:

1.  NewCol = Login[EMAIL]

This will just yield

2018-11-12

2019-01-12

in rows. Row context present. No issues.

2. NewCol=CALCULATE(Login[EMAIL])

This throws an error. As explained above, the row context has been transformed into filter context. The reference to a naked column with no row context is therefore not admitted.

3. NewCol=VALUES(Login[EMAIL])

There is a row context here but with no filter context, VALUES returns two values (2018-11-12,  2019-01-12) and therefore you get an error

4. NewCol=CALCULATE(VALUES(Login[EMAIL])

With the addition of CALCULATE, the row context has been transformed in filter context and thus VALUES( ) will return just one value per row. Same as in 1.

2018-11-12

2019-01-12