How to use the EARLIER function in DAX to sort, group, band and accumulate data
Part five 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.

1. The EARLIER function in DAX
2. Running totals using the EARLIER function in DAX
3. Using the EARLIER function to rank data
4. Group statistics using the EARLIER function
5. Banding using the EARLIER function (this blog)

This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.

Posted by Andy Brown on 13 January 2016

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

# Banding using the EARLIER function

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.

## Preparing for the example

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.

## Getting the price band for each transaction

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)

## Sorting the price bands

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!

This blog has 0 threads