Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago
Solved

Dynamic Row Numbers

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.

 

Need Help
  • v-haibl-msft's avatar
    v-haibl-msft
    10 years ago

    Anonymous

     

    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

10 Replies

  • v-haibl-msft's avatar
    v-haibl-msft
    Microsoft Employee
    10 years ago

    Anonymous

     

    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

    • RasikaOgale's avatar
      RasikaOgale
      Frequent Visitor
      9 years ago

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

    • elads's avatar
      elads
      Helper III
      6 years ago

      Hi Anonymous ,

       

      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] ) )
      )
    • jl20's avatar
      jl20
      Helper IV
      9 years ago

      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?

  • ankitpatira's avatar
    ankitpatira
    Community Champion
    10 years ago

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

     

    under Add Column tab click Add index column.

    • Anonymous's avatar
      Anonymous
      Not applicable
      10 years ago

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