DAX - Get current row number

0
votes

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.

1
daxrow-number

1 Answers

1
votes

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.