562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
How to use the EARLIER function in DAX to sort, group, band and accumulate data
Part one of a five-part series of blogs
Although the EARLIER function in DAX is complicated, it's also very useful! This blog shows how the function works, and how to use it to create running totals, sort rows, create group statistics and divide data into bands.
This is not only one of the most powerful functions in SSAS Tabular; it's also one of the most complicated.
The basic problem with this function is that it has a really confusing and misleading name! The EARLIER function allows us to refer to the current row while deciding which rows to pick to aggregate. Perhaps it should be called the CURRENTCONTEXTROW function?
In this blog I'll show how to use the EARLIER function to create the following:
|Example||What it asks to do|
|Running totals||Show for each row the cumulative total of all of the values with dates up to and including this row's date.|
|Ranking||Show for each row the number of rows whose price is more than this row's price.|
|Grouping||Show for each row the average price for all of the products whose target gender is the same as this row's target gender.|
|Banding||Find out in which price band each row lies.|
Reading through the examples above, you'll recognise that they all refer to each row. Here's how the EARLIER function works:
For each row of a table, SSAS Tabular aggregates data over the entire table, depending on the values of fields for the row in question.
If you know SQL, you may recognise that the EARLIER function is the equivalent of an SQL correlated subquery (although the database engine in SSAS will ensure that calculations using EARLIER run much more quickly than do the equivalent correlated subqueries in SQL).
Let's start with a look at how to create running totals in SSAS Tabular, using the EARLIER function.
|Parts of this blog|
25 Aytoun Street