cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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.

1 ACCEPTED SOLUTION
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] ) )
)

Best Regards,

Herbert

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

Best Regards,

Herbert

Super User

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

New Member

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

Helper III

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

Hi,

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

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!

Helper IV

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?

Frequent Visitor

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

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:

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

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors