ROWNUMBER

Applies to: Calculated column Calculated table Measure Visual calculation

Returns the unique ranking for the current context within the specified partition, sorted by the specified order. If a match cannot be found then then rownumber is blank.

Syntax

ROWNUMBER ( [ or ][, ][, ][, ][, ][, ] ) 

Parameters

• DEFAULT (the default value), where the behavior for numerical values is blank values are ordered between zero and negative values. The behavior for strings is blank values are ordered before all strings, including empty strings.
• FIRST, blanks are always ordered on the beginning, regardless of ascending or descending sorting order.
• LAST, blanks are always ordered on the end, regardless of ascending or descending sorting order.

Return value

The rownumber number for the current context.

Remarks

Each , , and column must have a corresponding outer value to help define the current row on which to operate, with the following behavior:

• If there is exactly one corresponding outer column, its value is used.
• If there is no corresponding outer column, then:
  • ROWNUMBER will first determine all , , and columns that have no corresponding outer column.
  • For every combination of existing values for these columns in ROWNUMBER parent context, ROWNUMBER is evaluated and a row is returned.
  • ROWNUMBER’s final output is a union of these rows.

  If is present, then ROWNUMBER will try to use columns in and to idenfity the current row.
  If the columns specified within and cannot uniquely identify every row in , then:

  • ROWNUMBER will try to find the least number of additional columns required to uniquely identify every row.
  • If such columns can be found, ROWNUMBER will
    • Try to find the least number of additional columns required to uniquely identify every row.
    • Automatically append these new columns to clause.
    • Sort each partition using this new set of orderBy columns.

    can be used in visual calculations only, and cannot be used in combination with or . If is present, can be specified but cannot.

    Example 1 - calculated column

    The following DAX query:

    EVALUATE ADDCOLUMNS( 'DimGeography', "UniqueRank", ROWNUMBER( 'DimGeography', ORDERBY( 'DimGeography'[StateProvinceName], desc, 'DimGeography'[City], asc), PARTITIONBY( 'DimGeography'[EnglishCountryRegionName]))) ORDER BY [EnglishCountryRegionName] asc, [StateProvinceName] desc, [City] asc 

    Returns a table that uniquely ranks each geography with the same EnglishCountryRegionName, by their StateProvinceName and City.

    Example 2 - visual calculation

    The following visual calculation DAX queries:

    SalesRankWithinYear = ROWNUMBER(ORDERBY([SalesAmount], DESC), PARTITIONBY([CalendarYear])) SalesRankAllHistory = ROWNUMBER(ORDERBY([SalesAmount], DESC)) 

    Create two columns that uniquely rank each month by the total sales, both within each year, and the entire history.

    The screenshot below shows the visual matrix and the first visual calculation expression: