Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
581 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers 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
Search our website
We also send out useful tips in a monthly email newsletter ...
Written by Andy Brown
In this tutorial
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.
To get this example to work, first bring the PosDate column from the Pos table into the Transaction table:
Use the RELATED function to bring the point-of-sale date for each transaction into the transactions table.
When travelling, it helps to keep the destination in sight. Here's what we want to achieve:
We'll create a new column giving the cumulative quantity (although this makes more sense if you sort the transactions by date, as below).
If you sort the transactions into date order, the figures make more sense:
Because the database doesn't record the time of the transaction, figures for each day's sales are lumped together.
Here's a formula which would achieve this:
=SUMX (
FILTER (
'Transaction',
'Transaction'[PosDate] <= EARLIER ( 'Transaction'[PosDate] )
),
'Transaction'[Quantity]
)
That is, instead of summing the quantity for the current row's transaction, sum it instead across the set of those rows whose transactions take place on a date up to and including the transaction date for the current row.
Don't forget (I often do) that this has to be the formula for a calculated column, and not a measure. Because it's referring to the value of the PosDate for the current row, a measure would make no sense and would return an error.
Next up - ranking using EARLIER.
A previous blog showed how you can rank data in DAX using the RANKX function; this page gives an alternative method, using EARLIER.
We'll rank the products by their list price. As we've seen in earlier blogs, The Emperor is the most expensive and Wol the cheapest.
Here's a formula for the calculated column (not a measure, don't forget!) to achieve this:
=COUNTROWS(
FILTER(
Product,
EARLIER([FullPrice])<[FullPrice]
)
)+1
To see how this works, consider the example of the first product in alphabetical order, Crocky:
The FILTER function returns the table of products, but filtered to show only those ones where the current row's FullPrice (11.50) is less than the product's full price. This gives the blue-shaded products: Pingu, Simon and The Emperor. The formula then counts how many rows there are in this set of rows, and adds one to it (this is needed because we want the top ranking product to be 1, not 0 or blank).
The calculation above is for one product; the formula will then be repeated again for the other 12 products. Although this look like it does 13 x 13 calculations, the database engine will optimise them to avoid repetition where possible.
The next worked example is on calculating group averages.
The worked example below shows how to calculate the average price of products by target gender:
What we're aiming for: showing the average price for each product by its target gender.
To get this case study to work, you'll first need to add the target gender table into your model:
You'll need to import this table into your model.
You'll also need to add the TargetGenderId column to the imported products table, and create a relationship between the Product and TargetGender tables:
Change the properties of the Product table to include the TargetGenderId column, and link the two tables together by this column. It's all good practice!
Finally, in preparation, display the TargetGenderName column within the Product table, using a RELATED function:
Create a calculated column to show the target gender within the products table.
Here's the formula to give the average price by target gender:
=AVERAGEX(
FILTER(
Product,
EARLIER([TargetGender])=[TargetGender]
),
[FullPrice]
)
To see how this works, consider the example of the first product, Crocky:
For Crocky, the FILTER function will return the set of rows shown here shaded: those for which the target gender equals this individual product's target gender. The formula will then take the average price from this set of rows.
The usual caveats apply: remember to create a calculated column and not a measure, and bear in mind that the database engine will calculate the results more efficiently than the above table would suggest.
Finally in this look at the EARLIER function, I'll show how to band rows together.
Previous examples in this tutorial have showed how to use the IF or SWITCH functions to divide transactions into price bands:
This example will divide transactions into these price bands.
The first thing to do is to create a separate table of price bands. One way to do this is to download this file. After unzipping it, you can then copy the table the Word document contains:
Click in the table, then click on the cross at the top left to select the entire table. You can then copy it to the clipboard in any of the usual ways.
In SSAS Tabular, go to Diagram view:
Switch to diagram view.
Paste in your table:
One way to paste in the table of data.
Change the table name from Table to PriceBand, and select OK:
It's very clever how this all works!
Your pasted table will be imported. You shouldn't link this to any other table:
The imported table of price bands.
The obvious attraction of this approach is that you could create your price bands in Excel or SQL Server, then update them without changing your model.
Here's how you can show the price band for each transaction:
Showing the price band for each transaction.
The formula for this calculated column is:
= CALCULATE (
VALUES ( PriceBand[PriceBandName] ),
PriceBand[BottomPrice] < EARLIER ( 'Transaction'[Price] ),
PriceBand[TopPrice] >= EARLIER ( 'Transaction'[Price] )
)
This takes a fair bit of explanation, I think! To see how this works, consider this transaction:
The price for this is £14.85, which is returned (correctly) as Expensive.
The VALUES function returns the PriceBandName from the PriceBand table where the BottomPrice is less than this transaction's price ...
The first condition states that the bottom price must be less than £14.85, which returns these 3 rows.
... but also where the TopPrice is greater than or equal to this transaction's price:
The second condition adds a requirement that the top price must be £14.85 or more.
This formula would return an error if the VALUES function returned more than one set of rows at any point, because a calculated column can't aggregate data or present more than one value. However, this shouldn't happen (you could always trap for this error with an IFERROR function to make the formula more robust)
You could now create a pivot table aggregating data by the price bands, but these wouldn't be sorted correctly (Expensive should be below Middling):
This pivot table sums the quantity sold by price band.
The obvious solution doesn't work. This would be to display the properties of the PriceBandName column:
Select this column and press F4 to bring up the properties window.
You could then choose to sort this column by the price band id, which happily for us would sort the price bands into the correct order:
Choose to sort the price band names by the id column (although it won't work ...).
If you didn't have such a convenient sorting column, you could always create one by adding a SortOrder column to the PriceBand table.
Sadly, the solution above doesn't work, because we're not displaying the PriceBandName column from the PriceBand table, but rather a calculated column we've concocted ourselves:
The sorting hasn't worked!
The solution is to create another calculated column in the transactions table which returns the sort order for each transaction:
The final sort order to use.
Here's the formula to use:
=CALCULATE (
VALUES ( PriceBand[PriceBandId] ),
PriceBand[BottomPrice] < EARLIER ( 'Transaction'[Price] ),
PriceBand[TopPrice] >= EARLIER ( 'Transaction'[Price] )
)
What this does is to return the id number of each price band for each transaction, rather than the name. You can now set this calculated column to be the sort order column for the PriceBand:
Set the sort order for the derived price band column as above, but this time use the PriceBandSortOrder that we've calculated.
Yeah!
The price bands are sorted correctly.
Although this is a lot of effort to set up, if you regularly add, delete or edit thresholds for bands the investment of time up-front will quickly pay off!
You can learn more about this topic on the following Wise Owl courses:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.