Search code examples
daxrow-number

DAX - Get current row number

The question seems to be easy but I found it really hard to be done in DAX. I'd like to get the current row number on my current context data. Same as ROW_NUMBER() in T-SQL.

Any hints?

Thanks in advance.

Solution

  • There is no such function. Closest you can get is to calculate rank based on sort-order, like:

    DEFINE MEASURE SomeTbl[ROW_NO] = SUMX(SomeTbl, RANKX(ALL(SomeTbl), SomeTbl[SortCol]))
EVALUATE ADDCOLUMNS(SomeTbl, "ROW_NO", SomeTbl[ROW_NO])

    Or if you can't use RANKX

    EVALUATE ADDCOLUMNS (
  SomeTbl, "ROW_NO", COUNTROWS(FILTER(SomeTbl, 
    EARLIER(SomeTbl[SortCol])<=SomeTbl[SortCol]))+0
)

    Note: for same values (of SomeTbl[SortCol]) the ROW_NO will be the same.

    If you're using DirectQuery mode you can also in model add extra column and define it likeSELECT *, ... ROW_NUMBER() as Foo - and use Foo column in DAX instead.