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.
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.
A table expression from which the output is returned.
An ORDERBY() clause containing the columns that define how each partition is sorted.
An enumeration that defines how to handle blank values when sorting.
A PARTITIONBY() clause containing the columns that define how Relation is partitioned.
A row at an absolute position.
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.
Other related functions are:
Contributors: Alberto Ferrari, Marco Russo, Daniil Maslyuk
Microsoft documentation: https://learn.microsoft.com/en-us/dax/index-function-dax