{"id":84835,"date":"2019-07-31T14:49:24","date_gmt":"2019-07-31T14:49:24","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=84835"},"modified":"2022-04-24T21:10:21","modified_gmt":"2022-04-24T21:10:21","slug":"cracking-dax-the-earlier-and-rankx-functions","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/cracking-dax-the-earlier-and-rankx-functions\/","title":{"rendered":"Cracking DAX \u2013 the EARLIER and RANKX Functions"},"content":{"rendered":"

The series so far:<\/strong><\/p>\n

    \n
  1. Creating Calculated Columns Using DAX<\/a><\/li>\n
  2. Creating Measures Using DAX<\/a><\/li>\n
  3. Using the DAX Calculate and Values Functions<\/a><\/li>\n
  4. Using the FILTER Function in DAX<\/a><\/li>\n
  5. Cracking DAX\u00a0\u2013 the EARLIER and RANKX Functions<\/a><\/li>\n
  6. Using Calendars and Dates in Power BI<\/a><\/li>\n
  7. Creating Time-Intelligence Functions in DAX<\/span><\/a><\/li>\n<\/ol>\n\n

    If you really want to impress people at DAX dinner parties (you know the sort: where people stand around discussing row and filter context over glasses of wine and vol-au-vents?), you\u2019ll need to learn about the EARLIER<\/code> function (and to a lesser extent about the RANKX<\/code> function). This article explains what the EARLIER<\/code> function is and also gives examples of how to use it. Above all, the article provides further insight into how row and filter context work and the difference between the two concepts.<\/p>\n

    Loading the Sample Data for this Article<\/h2>\n

    As for the other articles in this series, the sample dataset consists of four simple tables which you can download from this Excel workbook<\/a>:<\/p>\n

    <\/p>\n

    After importing the data, you should see this model diagram (I\u2019ve used Power BI, but the formulae in this article would work equally well in PowerPivot or SSAS Tabular):<\/p>\n

    <\/p>\n

    One more thing to do \u2013 add a calculated column to the country table, giving the total sales for each country:<\/p>\n

    TotalSales = CALCULATE(Sumx(Sales,[Price]*[Quantity]))<\/pre>\n
    <\/p>\n
    This formula iterates over the Sales<\/em> table, calculating the sales for each row (the quantity of goods sold, multiplied by the price) and summing the figures obtained. The CALCULATE<\/code> function is needed because you must create a filter context for each row so that you\u2019re only considering sales for that country \u2013 without it, you would get this:<\/p>\n
    <\/p>\n
    Example 1: Ranking the Countries<\/h2>\n
    The first use case is very simple. The aim is to rank the countries by the total sales for each:<\/p>\n
    <\/p>\n
    DAX doesn\u2019t have an equivalent of the SQL ORDER BY<\/code> clause, but you can rank data either by using the RANKX<\/code> function (covered later in this article) or by using the EARLIER<\/code> function creatively. Here\u2019s what the function needs to do, using country number 3 \u2013 India \u2013 as an example. Firstly, it creates a row context for this row:<\/p>\n
    <\/p>\n
    The sales for India are 34.5. What it now must do is to count how many rows there are in the table which have countries whose sales are greater than or equal to India\u2019s sales:<\/p>\n
    <\/p>\n
    This shows the filtered table of countries, including only those whose sales are at least equal to India\u2019s. There are four rows in this filtered table.<\/p>\n
    If you perform the same calculations for each of the other countries in the table, you\u2019ll get the ranking order for each (that is, the number of countries whose sales match or exceed each country\u2019s). For those who know SQL, this works in the same way as a correlated subquery. This would imply that it might run slowly, however, here\u2019s what Microsoft have to say<\/a> on the subject:<\/p>\n
    <\/p>\n
    The problem: One Row Context Hides Another<\/h3>\n
    Anyone who has driven much in France will have seen this sign at a level-crossing:<\/p>\n
    <\/p>\n
    What it means is \u201cone train can hide another\u201d … and so it is with row contexts. The measure will open two row contexts, as the diagram below shows:<\/p>\n
    <\/p>\n
    The problem is that when DAX gets to the inner row context for the FILTER<\/code> function, it will no longer be able to see the outer row context (the country you\u2019re currently considering) as this row context will be hidden unless you use the EARLIER<\/code> function to refer to the first row context created in the formula.<\/p>\n
    What Would You Name this Function?<\/h3>\n
    I\u2019ve explained that the EARLIER<\/code> function refers to the original row context created (nearly always for a calculated column). What would you have called this function? I\u2019d be tempted by one of these names:<\/p>\n