Dynamic Row Numbers

[taumirza]

Hello All,

Is there anyway to get Dynamic Row Numbers with a single column in every table.

For Ex: I have 2 Table viz with following data

every table has viz level filters so that i am showing the customer in different table viz's.

Tab1                                          Tab2

Customer       Revenue             Customer      Revenue

   A                     100                     M                  300

   B                     200                      X                  250

   N                     50                       Z                 1050

I want to insert a column as 'SL NO' at start which will give '1' to 'N' like this

Tab1                                                     Tab2

SL NO    Customer       Revenue          SL NO   Customer      Revenue

     1          A                     100                   1           M                  300

     2          B                     200                   2            X                  250

     3          N                     50                    3            Z                 1050      dynamically.

Thanks in Advance.

[v-haibl-msft · Accepted solution]

@taumirza

You can first add an index column and then create a measure with following formula to get the dynamic row numbers.

RowNum = 
CALCULATE (
    COUNT ( Table1[Index] ),
    FILTER ( ALLSELECTED ( Table1 ), Table1[Index] <= MAX ( Table1[Index] ) )
)

Best Regards,

Herbert

[ChielFaber]

Just found this solution while searching the forums. Works perfectly for me. Many thanks!

[Anusha1Anusha]

Hi Herbert,
Is there a method to have this dynamic indexing calculate in columns as it is not supported to lookup values from the prior index of the  column?
Thanks,
Anusha 

[elads]

Hi @taumirza ,

I tried to use your formula, but unfortunately it doesn't work (See attached image)

I am also attaching your formula, maybe I was wrong about something.

DynamicRowNumber =

CALCULATE (

count ( A_B[Index] ),

FILTER ( ALLSELECTED ( A_B ), A_B[Index] <= MAX ( A_B[Index] ) )

)

[radiare]

Hi,

Can you please explain the logic you wrote in this measure?

Thanks in advance

[Anonymous]

When i use this DAX to generate Current row number getting the error below when i added the measure to the table .please help me any ideas!

[jl20]

This doesn't work when sorting by a numeric measure (revenue, in your example). The formula does limit the numbers from 1 to number of displayed rows, but the Row numbers are not dynamic... Any ideas on how to fix?

[RasikaOgale]

I also want to apply same in my project. I have created Index column and then applied mentioned formula, but isn't work. 

[ankitpatira]

@taumirza if i understood you correctly, go to power bi desktop query editor,

under Add Column tab click Add index column.

[taumirza]

No @ankitpatira.

Thiz what i get when I try to use index Column which in fact is a static value for every row:

Vis Filters: ScenarioName = 'Budget' for Table1

                  ScenarioName  = 'Actual and Forecast' for Table2 .

But I need Outpu like this:

ScenarioName   Index1                   ScenaroName   Index1

 Budget                 1                          Forecast              1

                                                          Actual                 2

I want Index to be start from 1 in Every table for index column which in the sence must be a dynamic calculation which gives valuse from 1 to n every time we use it (like row number in (Cognos and SSRS).