DAX

Dax Function: INDEX

Q: What is the difference between INDEX and TOPN?

INDEX retrieves a single row or value based on position, while TOPN retrieves multiple rows based on rank or condition.

Q: Does INDEX support zero-based indexing?

Yes, use MatchMode = 0 for zero-based indexing.

Category: Filter Functions

The INDEX function in Power BI is a DAX function that retrieves a single value or row based on its position within a table or a specified range. It is commonly used for positional lookups within a dataset.

Purpose

The INDEX function allows you to:

Access specific rows or values based on their position in a table or list.
Simplify complex calculations by pinpointing exact elements.
Enhance dynamic reporting with precise data retrieval.

Type of Calculations

This function performs:

Row-level operations to retrieve specific entries.
Positional lookups, including dynamic references.
Context-aware evaluations within sorted or unsorted datasets.

Practical Use Cases

Dynamic Row Selection: Retrieve a row based on its index to create flexible reports.
Ranking Analysis: Access ranked positions in a sorted dataset.
Paginated Reports: Dynamically select data for paginated visuals.


INDEX(<rowindex>, <table>, [<orderbyexpression1>], [<orderbyexpression2>], …, [<matchmode>])</matchmode></orderbyexpression2></orderbyexpression1></table></rowindex>

Parameter	Type	Description
`RowIndex`	Integer	The position (row index) to retrieve from the table.
`Table`	Table	The table or range from which the value is retrieved.
`OrderByExpression1`	Expression	(Optional) The column or expression used to sort the table.
`OrderByExpression2`	Expression	(Optional) Additional expressions for sorting.
`MatchMode`	Integer	(Optional) Specifies whether the indexing is zero-based (`0`) or one-based (`1`).

How Does INDEX Dax Works

The function evaluates the specified table or dataset.
Optional OrderByExpression parameters are applied to sort the table.
The specified RowIndex is used to locate and retrieve the corresponding value or row.
The MatchMode determines if the indexing starts from 0 or 1.

Logical Flow

Sort the Data (if OrderByExpression is provided).
Retrieve the Position specified by RowIndex.
Output the value or row.

Example Formula:

Top Sales Row = INDEX(1, Sales, Sales[Amount], DESC)

This retrieves the first row with the highest sales amount.

What Does It Return?

Row or Value: The function returns a single value or row based on the specified index. If no row matches the index, it returns BLANK.

When Should We Use It?

Rank-Based Analysis: Extract data for top or bottom ranks.
Data Navigation: Dynamically navigate to specific positions.
Flexible Reporting: Enhance reports with positional data retrieval.

Examples

Basic Usage :


INDEX(2, Products)

Returns the second row from the Products table.

Column Usage


Second Highest Sales = INDEX(2, Sales, Sales[Amount], DESC)

Retrieves the second row when sorted by sales amount in descending order.

Advanced Usage


Top Product by Region =
INDEX(
1,
FILTER(Products, Products[Region] = "North"),
Products[Sales],
DESC
)

Retrieves the top-selling product in the “North” region.

Tips and Tricks

Zero-Based Indexing: Use MatchMode = 0 for zero-based indexing in advanced calculations.
Sort Consistently: Ensure the dataset is sorted correctly for reliable results.
Combine with Filters: Pair with FILTER or CALCULATE for dynamic row selection.

Performance Impact of INDEX DAX Function:

Efficiency: The function is optimized for small to medium datasets. For larger datasets, pre-sorting may improve performance.
Sorting Overhead: Avoid unnecessary sorting for better speed.

Related Functions You Might Need

RANKX: Assigns rank numbers based on a sort order, often used before INDEX.
FILTER: Filters the dataset to narrow down results for indexing.
TOPN: Retrieves a specific number of top rows.
LASTNONBLANKVALUE: Retrieves the last non-blank value, complementing the positional lookup of INDEX.

Want to Learn More?
For more information, check out the official Microsoft documentation for INDEX You can also experiment with this function in your Power BI reports to explore its capabilities.

Unlock the full capabilities of Power BI and elevate your data insights with our specialized consulting services. Whether you need guidance on advanced DAX functions like those highlighted here, support in designing interactive dashboards, or expertise in optimizing data models for enhanced performance, our experienced Power BI consultants are equipped to deliver customized solutions for your business. Explore our Power BI Consulting Services page to discover how we can help your organization make smarter, data-driven decisions.

1. What is the difference between INDEX and TOPN?

INDEX retrieves a single row or value based on position, while TOPN retrieves multiple rows based on rank or condition.

2. Does INDEX support zero-based indexing?

Yes, use MatchMode = 0 for zero-based indexing.

3. Can I use INDEX without sorting the table?

Yes, but the order of rows may not be predictable unless sorted.

4. What happens if the RowIndex exceeds the table size?

The function returns BLANK.

5. Is INDEX case-sensitive when filtering text values?

No, it inherits Power BI’s case-insensitivity for text comparisons.

Dax Function: FIRSTDATE

Dax Function: KEEPFILTERS