What is the DAX equivalent of Row Number() in SQL?

Question

I want to add an column in my DAX query output which will give the cumulative row number starting from 1? I want to generate this dynamically for any DAX query that I run. How can I achieve this without much performance impact?

I have a dataset like this:

I have created a measure for ContractValue as SUM([Value]). The expected output is to get the dynamic row number generated for the aggregated results as well. For example:

community.powerbi.com/t5/Desktop/Dynamic-Row-Numbers/td-p/52671 — RADO
Hi, thanks for the link. But here instead of adding the Index column at the table level, Can we do something similar at runtime for the DAX output? — Sananda Dutta
Unless you are talking about Ranking on a specific known set of field, something similar with RANKX can be achieved. You won't have a true row_number, you can have repetition ou missing values. DENSE vs SKIP options. RANK = RANKX(ALL('mytable'[Confidential]),SUM('mytable'[Value]),,DESC,DENSE) If you want this to be generic for the whole model... There isn't a straight foward way to achieve this. — mxix

Aldert Aldert · Accepted Answer · 2019-07-14T19:03:45

You can first create your summary table:

ContractSummary =
GROUPBY (
    Contract;
    Contract[Confidential];
    "Contract Value"; SUMX ( CURRENTGROUP (); Contract[Value] );
    "RowCount"; COUNTX ( CURRENTGROUP (); Contract[Value] )
)

Next you can add a column to your table:

Expected Result =
CALCULATE (
    COUNT ( ContractSummary[Contract Value] );
    FILTER (
        ContractSummary;
        ContractSummary[Contract Value] >= EARLIER ( ContractSummary[Contract Value] )
    )
)

What is the DAX equivalent of Row Number() in SQL?

1 Answers