How to simply number rows in the visual?

[Anonymous]

I'm trying to do soemthing that seems like it should be very basic, but I've not had luck finding information on it. I want a column in a simple table visual to simply number the row, regardless of how the table is filtered or sorted. So Row 1 would always read "1," Row 2 would always read "2," and so on. If the table is sorted by a different column or a different filter is applied, the number of the row would not change. 

When I look for information on this, I keep finding posts on index columns and a commonly cited formula that does not seem to do what I'm looking for. Or maybe I'm not applying it correctly?

Row_Number = 
CALCULATE (
    COUNTROWS(Table),
    FILTER ( ALLSELECTED ( Table ), Table[Column] <= MAX ( Table[Column]) )
)

When I use this measure on an index column, it will number the rows based on the orignal data, but it won't adjust for the actual number of rows displayed in the visual. So if there are 1,000 rows in the original data, this formula will label Row 999 correctly, but if I apply a filter in the visual that removed the first 200 rows, the row that was labeled 999 still has that entry, despite the fact that it is no longer Row 999 in the visual. 

It seems like this should be a toggle button option somewhere in the formatting to simply show the row numbers, but I can't find anything on this. 

Is what I'm looking for even possible? Is there a great big button that does exactly this that I haven't seen? 

[Anonymous]

The formula you post works fine IF

you use the Table[Column] on the INDEX column.

So you create an index column in the edit queries window. Then create the measure like this:

Row_Number = 
CALCULATE (
    COUNTROWS('Table');
    FILTER ( ALLSELECTED ('Table'); 'Table'[Index] <= MAX ( 'Table'[Index]) )
)

tested it and it works.

[Anonymous]

I've tried this, and it seems to create a fixed row label. Unless I'm missing something else. 

What I'm trying to do is basically just how a normal spreadsheet works, where the row number is the same regardless of what the content is. You can sort and filter and removed and add all you want, and Row 1 is always Row 1, no matter what moves to Row 1. 

The forumla above will label the rows initially, but if I sort the table by a different column or filter out certain columns, the numbering is no longer correct. I don't want to label the row content, I want to label the row itself. 

[Anonymous]

So to get it absolutely correct: 

When i have the following table:
MonthData

Jan	Data 1
Jan	Data 2
Jan	Data 3
Feb	Data 4
Feb	Data 5
Feb	Data 6
Mar	Data 7
Mar	Data 8
Mar	Data 9
Mar	Data 10

You want to see
IndexMonthData

1	Jan	Data 1
2	Jan	Data 2
3	Jan	Data 3
4	Feb	Data 4
5	Feb	Data 5
6	Feb	Data 6
7	Mar	Data 7
8	Mar	Data 8
9	Mar	Data 9
10	Mar	Data 10

But when you filter on ¨Feb¨

You want to see:
 

1	Feb	Data 4
2	Feb	Data 5
3	Feb	Data 6

Correct?

[Anonymous]

Exactly! The goal is for the column to label the row itself, irrespective of the content of the row. 

[Anonymous · Accepted solution]

Ok so lets go:
I started with this table:
MonthData

Jan	Data 1
Jan	Data 2
Jan	Data 3
Feb	Data 4
Feb	Data 5
Feb	Data 6
Mar	Data 7
Mar	Data 8
Mar	Data 9
Mar	Data 10

Then went to Edit queries > Add column > Index Column. 
This will create this:
IndexMonthData

0	Jan	Data 1
1	Jan	Data 2
2	Jan	Data 3
3	Feb	Data 4
4	Feb	Data 5
5	Feb	Data 6
6	Mar	Data 7
7	Mar	Data 8
8	Mar	Data 9
9	Mar	Data 10

This number is indeed not going to change dynamicly BUT. We need it for our measure to work.
Save and apply the changes.
Now i created a visual with the starting table and a slicer on Month:

As you can see the months look wierd because of alphabetical order, not going to bother about that atm. But this is our starting point.
Next step is to create a measure with the following code:

Row_Number = 
CALCULATE (
    COUNTROWS('Table');
    FILTER ( ALLSELECTED ('Table'); 'Table'[Index] <= MAX ( 'Table'[Index]) )
)

And add it to our table:

I hear you thinking; This is not what i want. But hold on. It does indeed look wierd now because it calculated Januari as the first rows in the data set, so it gave them the first number. When you sort the items on Row Number it looks clean again:

Now, because it is dynamic in the measure, when you for example select Mar. the visual will look like this:

Since Mar is then the first row, its gets number 1 🙂

Hope this helps.