Hi,
I will try to explain the purpose of EARLIER function using an example.
Assume that we have the following table.
Table Name: USERS
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.
