Add row number/rank to ssas tabular model

Question

I'm trying to add row numbers (by some sorting column) or at least ranks into a calculated table inside azure analysis services tabular model (I need this rank column to use in TOPNSKIP, it should be precalculated column in the model's table. Really it will not be precalculated column for one sorting column only. It will be some expression I want to have precalculated). I used some thoughts from here DAX - Get current row number

I use Visual Studio with Microsoft Analysis Services Projects extension to deploy the model to ssas and SSMS to debug the requests. But it seems there are differences between dax requests in the SSMS and in the tabular models definitions.

The SSMS uses evaluate syntaxis and the tabular constructor = VAR RETURN. And it seems some functions are not available for the tabular models. It seems like ADDCOLUMNS and RANKX are not available for tabular models. Also , is used as operand separator in the SSMS and ; in the model DAX editor, (possible due to some internationalization options on my computer) and that is not very convenient. I could not find a way to get error report for the bad DAX request for the model definition. I only see for incorrect request that has been retrieved 0 records while I deploy a model and if I try to do some request to a table after deploying I get error message that it's empty because of incorrect construction, but no details. There is also a possibility to use calculated columns during the table construction, but I could not embed the RANKX function to the formula for them. Also I don't know how to use a MEASURE inside a construction function. The filter approach from the mentioned stackoverflow question was very slow on my amount of data. I was waiting about half an hour and stopped the deploying.

Because I don't see the ADDCOLUMNS works I've tried GENERATE and SELECTCOLUMNS. For example:

=
VAR NewTable = GENERATE(BaseTable; ROW("RowNumber"; RANKX(BaseTable; BaseTable[SortName];;1)))
RETURN NewTable

It works nice in the SSMS but can't be used to construct a table. While:

=
VAR NewTable = GENERATE(BaseTable; ROW("RowNumber"; 50))
RETURN NewTable

works fine everywhere. So I think RANKX is not working in the tabular model definitions.

Is there any way to rank or number rows in ssas calculated table by some sorting column?

Why do you need to add a RowNumber column? You can specify a sort order when using TOPNSKIP directly... — Dan
@Dan It's just an example. In topnskip I can add sort order only for one column, or use some expression. Really I want to do more complicated sorting and want to have things precalculated. — Роман Коптев
@Dan Also I don't see I can use skip parameter if OrderByExpression is epression using RANKX functions and not just a column existing in the real base table — Роман Коптев

Dan Dan · Accepted Answer · 2020-02-07T10:26:43

Adding high cardinality columns such as an index number column is generally not advisable for tables in a tabular model, since these columns compress very very badly, thereby reducing query performance. Especially if the table is large. But if you must do it, use the technique shown here. If you don't want to add a calculated column to an existing table, you can wrap everything in a call to ADDCOLUMNS:

= 
ADDCOLUMNS(
    BaseTable, 
    VAR CurrentSortName = BaseTable[SortName]
    RETURN
        COUNTROWS(
            FILTER(BaseTable, BaseTable[SortName] < CurrentSortName)
        ) + 1
)

Add row number/rank to ssas tabular model

1 Answers