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. |
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.
Related functions
Other related functions are:
Last update: Jan 31, 2023 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo, Daniil Maslyuk
Microsoft documentation: https://learn.microsoft.com/en-us/dax/index-function-dax