Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Anonymous
Not applicable

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.

 

1 ACCEPTED SOLUTION
v-haibl-msft
Employee
Employee

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

Dynamic Row Numbers_1.jpgDynamic Row Numbers_2.jpg

 

Best Regards,

Herbert

View solution in original post

10 REPLIES 10
v-haibl-msft
Employee
Employee

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

Dynamic Row Numbers_1.jpgDynamic Row Numbers_2.jpg

 

Best Regards,

Herbert

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

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 

Hi @Anonymous ,

 

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

 

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

Hi,

 

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

Thanks in advanceMan Happy

Anonymous
Not applicable

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!

 

 

Error.jpg

 

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?

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

ankitpatira
Community Champion
Community Champion

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

 

under Add Column tab click Add index column.

Anonymous
Not applicable

No @ankitpatira.

 

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

1.png

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

 

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.