INDEX DAX Function (Filter)
Retrieves a row at an absolute position (specified by the position parameter) within the specified partition sorted by the specified order or on the axis specified.
Syntax
Parameter | Attributes | Description |
---|---|---|
Position |
The absolute position (1-based) from which to obtain the data:
When Position is out of the boundary, or zero, or BLANK(), INDEX will return an empty table. It can be any DAX expression that returns a scalar value. |
|
Relation | Optional |
A table expression from which the output is returned.
|
OrderBy | Optional |
An ORDERBY() clause containing the columns that define how each partition is sorted.
|
Blanks | Optional |
An enumeration that defines how to handle blank values when sorting. |
PartitionBy | Optional |
A PARTITIONBY() clause containing the columns that define how Relation is partitioned. |
MatchBy | Optional |
Columns that define how the current row is identified. |
Reset | Optional |
Specifies how the calculation restarts. Valid values are: None, LowestParent, HighestParent, or an integer. |
Return values
A row at an absolute position.
Remarks
Each PartitionBy column must have a corresponding outer value to help define the “current partition” on which to operate, with the following behavior:
- If there is exactly one corresponding outer column, its value is used.
- If there is no corresponding outer column:
- INDEX will first determine all PartitionBy columns that have no corresponding outer column.
- For every combination of existing values for these columns in INDEX’s parent context, INDEX is evaluated and a row is returned.
- INDEX’s final output is a union of these rows.
- If there is more than one corresponding outer column, an error is returned.
If the non-volatile columns specified within OrderBy and PartitionBy cannot uniquely identify every row in Relation:
- INDEX will try to find the least number of additional columns required to uniquely identify every row.
- If such columns can be found, INDEX will automatically append these new columns to OrderBy, and each partition is sorted using this new set of OrderBy columns.
- If such columns cannot be found, an error is returned.
An empty table is returned if:
- The corresponding outer value of a PartitionBy column does not exist within Relation.
- The Position value refers to a position that does not exist within the partition.
If INDEX is used within a calculated column defined on the same table as Relation and OrderBy is omitted, an error is returned.
» 4 related functions
Examples
EVALUATE VAR BrandsAndSales = ADDCOLUMNS ( ALL ( 'Product'[Brand] ), "@Sales", [Sales Amount] ) RETURN INDEX ( 2, BrandsAndSales, ORDERBY ( [@Sales], DESC ) )
Brand | @Sales |
---|---|
Contoso | 2,227,244.32 |
EVALUATE SUMMARIZECOLUMNS ( Product[Brand], 'Date'[Year], "Sales", [Sales Amount], "Sales First Year", CALCULATE ( [Sales Amount], INDEX ( 1, ORDERBY ( 'Date'[Year] ) ) ) )
Product[Brand] | Date[Year] | Sales | Sales First Year |
---|---|---|---|
Contoso | 2,017 | 511,810.85 | 511,810.85 |
Contoso | 2,018 | 868,179.21 | 511,810.85 |
Contoso | 2,019 | 707,010.55 | 511,810.85 |
Contoso | 2,020 | 140,243.70 | 511,810.85 |
Wide World Importers | 2,017 | 483,427.60 | 483,427.60 |
Wide World Importers | 2,018 | 737,726.73 | 483,427.60 |
Wide World Importers | 2,019 | 458,943.97 | 483,427.60 |
Wide World Importers | 2,020 | 116,832.70 | 483,427.60 |
Northwind Traders | 2,017 | 21,598.14 | 21,598.14 |
Northwind Traders | 2,018 | 64,737.97 | 21,598.14 |
… | … | … | … |
Related articles
Learn more about INDEX in the following articles:
-
Introducing window functions in DAX
In December 2022, DAX was enriched with window functions: INDEX, OFFSET, and WINDOW. This article introduces the syntax and the basic functionalities of these new features. » Read more
-
SQLBI+ updates in May 2023
In 2023, we released the first draft of the Window functions in DAX whitepaper as part of SQLBI+. Since then, we have released a few updates and are now glad to announce the availability of the related 3-hour video course… » Read more
Related functions
Other related functions are:
Last update: Dec 4, 2024 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo, Daniil Maslyuk
Microsoft documentation: https://learn.microsoft.com/en-us/dax/index-function-dax