cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Cannot use EARLIER() in ADDCOLUMNS()

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Cannot use EARLIER() in ADDCOLUMNS()

@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

 

View solution in original post

5 REPLIES 5
Super User
Super User

Re: Cannot use EARLIER() in ADDCOLUMNS()

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.

Super User
Super User

Re: Cannot use EARLIER() in ADDCOLUMNS()

@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

 

View solution in original post

Re: Cannot use EARLIER() in ADDCOLUMNS()

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.

Highlighted
Super User
Super User

Re: Cannot use EARLIER() in ADDCOLUMNS()

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

Re: Cannot use EARLIER() in ADDCOLUMNS()

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.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 334 members 3,002 guests
Please welcome our newest community members: