0
votes

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:

enter image description here

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:

enter image description here

1
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
What defines the order of the rows in your ouput? - RADO
The Contract Value measure. - 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

1 Answers

0
votes

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] )
    )
)