Tableau INDEX function is used to return the position of a specified field or column. It returns the index number of the first occurrence of the value in the specified field or column. Its always start with 1. The INDEX() function does not take any arguments.
However, it requires a field or column to be dragged into the view. When the function is applied to the field or column, Tableau returns the position of the first occurrence of the value.
Below is the simple syntax for the INDEX() function in Tableau:
INDEX()
How to use the index function in Tableau?
Tableau Index function can be used in the calculated field to show the Index number or the position of rows for any specific dimensions. It will also help in the complex calculation, combined with other functions like Lookup, Rank etc..
How to create an index in Tableau?
You can follow below steps to create Index in tableau
- First open your tableau desktop
- Now insert new sheet in your tableau desktop
- Now drag the dimension [Region] in row, which you would like to use in table
- Now create a new Calculated Field called Index
- Put formula INDEX()
- Now drag index Calculated Field in front of [region] field. it will show the Index numbers of rows.
What are the difference between Rank and index function in Tableau?
| RANK() Function | INDEX() Function |
|---|---|
| Rank function computes based on the measures values and find which one is the highest values, then its Rank the rows | Index function computes based on the physical position of the rows in table, regardless of the values |
| Rank function syntax is: RANK(Expression, [Asc/Desc]) | Index function syntax is: INDEX() |
| Rank function asked for the arguments or expression within function | Index works without expression or arguments |
| It can use to find out the top performer using rank function | It can only index the rows |
Key features of the Tableau INDEX function:
- It is a table calculation function that is used to return the position of a specified value within a specified field or column.
- It can also be used to find the rank of a specific value in a field or column.
- It can often used in combination with other table calculation functions, such as RANK(), Lookup() to create complex calculations.
- It can be used to find the position of a value within a partition of data, based on the specified sort order.
Note:
The first position returned by the INDEX() function is 1, not 0.
lets understand more with 5 examples of the INDEX() function in Tableau with syntax:
Example #1: Find the position of rows using Tableau INDEX function
Just take first example as simple example to show the index and positions of the rows of any dimension, To find the position of the first occurrence of a value in a field or column:
INDEX()
Lets consider the field is Region then output ill look like as shown below image, you can see the Index numbering of rows start with 1.
Example #2: Find the position of rows start with specific field
Now lets try to find the position of rows in a specific field, so example in [Region] filed we would like to get position of “North America” but counting/indexing start from “Europe” instead of “Asia”, then you need to mention “-1” in the formula as shown below
INDEX() - 1
And output will look like as shown below image, Now Index number of “North America” is 5 now, in example #1 index number was 6
Example #3: Find Index in tableau for each sub-category field
If you will use simple Tableau INDEX() function calculated Table(Down) way, then it will show Index number for sub category in the ascending order numbering as shown below image
Here the sub category “” have continues Index lumber like 8, 9, 10, 11, so on, which is not needed some time.
If you want same index number like 1, 2, 3, 4 so on then you need to calculate table (across) OR Pan(Down) way, as shown below
Output for each sub-category will look like as shown below image
How can change Table (down) to Table (across) calculation in Tableau?
- Right click on Measures
- Click on “Edit table calculation …” option
- Now select Pan(down) option which is equal to table (cross) option
Example #4: Show top N numbers of rows only
This is great tableau INDEX function trick to show N numbers of rows in your table or bar chart view. lets take example you would like to see first 5 rows only in the table then you can use below calculated field.
Then put this calculated field in Filter option > and select TRUE option
INDEX() <= 5
then Output will look like as shown below image
You can also use the same calculated field in Chart visualization also.