Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Explanation of the EARLIER formula

Dear all,

I have read about the earlier function but It doesnt seen to make sense to me.
If possible could somebody give a clariying explanation with an example?

Many thanks
1 ACCEPTED SOLUTION
sreenathv
Solution Sage
Solution Sage

Hi,

 

I will try to explain the purpose of EARLIER function using an example.

 

Assume that we have the following table.

 

Table Name: USERS

 

User

Country

A

X

B

X

C

X

D

Y

E

Y

F

Z

 

If we need a calculated column that gives the count of other users from the same country against each user, we can use EARLIER function.

UsersFromSameCountry =
SUMX ( Users, IF ( EARLIER ( Users[Country] ) = Users[Country], 1, 0 ) )

 

SUMX will iterate over the table "Users" by creating a row context for every row in the table. i.e. it will iterate over the record of each user from A to F.

 

Although it looks like we have not created any nested row context in the formula mentioned above, in reality, whenever a calculated column is defined using a DAX formula, the column definition itself creates a row context for evaluating a different value for each row using the same formula. That means, when we defined the calculated column, the formula is executed for each row in the table. As a result, there exists a nested row context. One created by the calculated column definition and one created by the aggregation function - SUMX.

 

The table Users has 6 pairs of User-Country combinations A-X,B-X,C-X,D-Y,E-Y,F-Z and therefore for each pair, the SUMX will iterate over the table Users to count the number of users from the same country. For the first pair, the IF condition is executed 6 times. 

 

User

Country

Iteration User

Iterated Country

IF Condition

IF Condition Result

A

X

A

X

X=X?

1

A

X

B

X

X=X?

1

A

X

C

X

X=X?

1

A

X

D

Y

X=Y?

0

A

X

E

Y

X=Y?

0

A

X

F

Z

X=Z?

0

 

   

SUMX Result

3

 

The result of SUMX for the first row context (A-Z pair) will be 3. Similarly the SUMX will iterate over the table Users 5 more times for each of the remaining User-Country pairs.

 

If you look at the example given in the table above, the IF condition is comparing the Users[Country] field with itself, but only the row context varies.

 

Had we written the formula as...

SUMX ( Users, IF ( Users[Country] = Users[Country], 1, 0 ) )

Then the IF condition would always be true and the result of SUMX will always be 6 for each row in the table.

 

By writing the formula as

SUMX ( Users, IF ( EARLIER ( Users[Country] ) = Users[Country], 1, 0 ) )

We will be able to compare the value of Users[Country] field of SUMX's iteration with each row in the table (row context created by the calculated column definition) and thereby giving the correct result as follows...

 

User

Country

UsersFromSameCountry

A

X

3

B

X

3

C

X

3

D

Y

2

E

Y

2

F

Z

1

 

In short, EARLIER is used to refer to the outer row context in case of a nested row context. The nesting of row contexts can be done either explicitly writing a DAX code by the user or by the DAX engine when an aggregate function is used in the calculated column definition.

View solution in original post

6 REPLIES 6
theov
Advocate II
Advocate II

EARLIER is explained really to bits in this video with practical examples. 
Watch this to understand it fully and deepen your DAX 🙂 
https://www.youtube.com/watch?v=q8zsWqcd1vM

Ola_S
Frequent Visitor

 

= SUMX(Users, - -(EARLIER(Users[Country])=Users[Country]))

 

Note: - - is a Power BI trick, based on the old -- trick in Excel.

sreenathv
Solution Sage
Solution Sage

Hi,

 

I will try to explain the purpose of EARLIER function using an example.

 

Assume that we have the following table.

 

Table Name: USERS

 

User

Country

A

X

B

X

C

X

D

Y

E

Y

F

Z

 

If we need a calculated column that gives the count of other users from the same country against each user, we can use EARLIER function.

UsersFromSameCountry =
SUMX ( Users, IF ( EARLIER ( Users[Country] ) = Users[Country], 1, 0 ) )

 

SUMX will iterate over the table "Users" by creating a row context for every row in the table. i.e. it will iterate over the record of each user from A to F.

 

Although it looks like we have not created any nested row context in the formula mentioned above, in reality, whenever a calculated column is defined using a DAX formula, the column definition itself creates a row context for evaluating a different value for each row using the same formula. That means, when we defined the calculated column, the formula is executed for each row in the table. As a result, there exists a nested row context. One created by the calculated column definition and one created by the aggregation function - SUMX.

 

The table Users has 6 pairs of User-Country combinations A-X,B-X,C-X,D-Y,E-Y,F-Z and therefore for each pair, the SUMX will iterate over the table Users to count the number of users from the same country. For the first pair, the IF condition is executed 6 times. 

 

User

Country

Iteration User

Iterated Country

IF Condition

IF Condition Result

A

X

A

X

X=X?

1

A

X

B

X

X=X?

1

A

X

C

X

X=X?

1

A

X

D

Y

X=Y?

0

A

X

E

Y

X=Y?

0

A

X

F

Z

X=Z?

0

 

   

SUMX Result

3

 

The result of SUMX for the first row context (A-Z pair) will be 3. Similarly the SUMX will iterate over the table Users 5 more times for each of the remaining User-Country pairs.

 

If you look at the example given in the table above, the IF condition is comparing the Users[Country] field with itself, but only the row context varies.

 

Had we written the formula as...

SUMX ( Users, IF ( Users[Country] = Users[Country], 1, 0 ) )

Then the IF condition would always be true and the result of SUMX will always be 6 for each row in the table.

 

By writing the formula as

SUMX ( Users, IF ( EARLIER ( Users[Country] ) = Users[Country], 1, 0 ) )

We will be able to compare the value of Users[Country] field of SUMX's iteration with each row in the table (row context created by the calculated column definition) and thereby giving the correct result as follows...

 

User

Country

UsersFromSameCountry

A

X

3

B

X

3

C

X

3

D

Y

2

E

Y

2

F

Z

1

 

In short, EARLIER is used to refer to the outer row context in case of a nested row context. The nesting of row contexts can be done either explicitly writing a DAX code by the user or by the DAX engine when an aggregate function is used in the calculated column definition.

Hi @sreenathv 

 

It's very useful and impoartant explanataion. Thank you so much.

 

I just wonder,

 

If I have a data table like below,

How can I create a new column (below DESIRED COLUMN) which is calculated by Value grouped by Compnay as average.

 

I wrote this dax but It returned average of Page no?!

How can get Average of Value for each page. returning value must be in every its row.

Average of Same page's value = AVERAGEX(AEX , IF(EARLIER(AEX[pageno]) = AEX[pageno] , 1 , 0))
 
CompnayPage NoValueDESIRED COLUMN
A23070
A24070
B210100
B240100
B350100
C11060
C12060
C32060
C31060
TomMartens
Super User
Super User

Hey,

 

would have been helpful if you provided what you have been reading so far.

 

Basically, EARLIER provides access to values from a certain filter context by default the innermost filter context. This can become quite handy if nested filter contexts exist.

 

Nowadays I would recommend to use variables instead of using EARLIER.

This article explains why to use variables instead of EARLIER:

https://www.sqlbi.com/articles/variables-in-dax/

 

Regards,

Tom 



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
MFelix
Super User
Super User

Hi @Anonymous,

 

Check the two links below with explanations and examples of the EARLIER sintax.

 

https://exceleratorbi.com.au/earlier-vs-earliest-dax/

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

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.