INDEX

INDEX is a DAX function that retrieves a row at a specific position within a partition sorted by a specified order or axis.

Syntax

INDEX(
   Position,
   Relation,
   OrderBy,
   Blanks,
   PartitionBy,
   MatchBy,
   Reset
)

Argument	Properties	Description
Position		The absolute position (1-based) from which to obtain the data.
Relation	Optional	A table expression from which the output will be returned. If omitted, OrderBy must be explicitly specified.
OrderBy	Optional	Columns that define how each partition is sorted. If omitted, Relation must be explicitly specified.
Blanks	Optional	An enumeration used to define how to handle BLANK OrderBy values. Valid value is DEFAULT for now.
PartitionBy	Optional	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 and matchBy 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 and matchBy 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 matchBy is present, INDEX will try to use matchBy and partitionBy columns to identify the row. If matchBy is not present and the 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.

reset can be used in visual calculations only, and cannot be used in combination with orderBy or partitionBy. If reset is present, axis can be specified but relation cannot.

The following DAX query:

EVALUATE INDEX(1, ALL(DimDate[CalendarYear]))

Returns the following table:

DimDate[CalendarYear]
2005

The following DAX query:

EVALUATE
SUMMARIZECOLUMNS (
    FactInternetSales[ProductKey],
    DimDate[MonthNumberOfYear],
    FILTER (
            VALUES(FactInternetSales[ProductKey]),
            [ProductKey] < 222
    ),
    "CurrentSales", SUM(FactInternetSales[SalesAmount]),
    "LastMonthSales",
    CALCULATE (
        SUM(FactInternetSales[SalesAmount]),
        INDEX(-1, ORDERBY(DimDate[MonthNumberOfYear]))
    )
)
ORDER BY [ProductKey], [MonthNumberOfYear]

Returns the following table:

FactInternetSales[ProductKey]	DimDate[MonthNumberOfYear]	[CurrentSales]	[LastMonthSales]
214	1	5423.45	8047.7
214	2	4968.58	8047.7
214	3	5598.4	8047.7
214	4	5073.55	8047.7
214	5	5248.5	8047.7
214	6	7487.86	8047.7
214	7	7382.89	8047.7
214	8	6543.13	8047.7
214	9	6788.06	8047.7
214	10	6858.04	8047.7
214	11	8607.54	8047.7
214	12	8047.7	8047.7
217	1	5353.47	7767.78
217	2	4268.78	7767.78
217	3	5773.35	7767.78
217	4	5738.36	7767.78
217	5	6158.24	7767.78
217	6	6998	7767.78
217	7	5563.41	7767.78
217	8	5913.31	7767.78
217	9	5913.31	7767.78
217	10	6823.05	7767.78
217	11	6683.09	7767.78
217	12	7767.78	7767.78

The following visual calculation DAX queries:

SalesComparedToBeginningOfYear = [SalesAmount] - CALCULATE(SUM([SalesAmount]), INDEX(1, ROWS, HIGHESTPARENT))

SalesComparedToBeginningOfQuarter = [SalesAmount] - CALCULATE(SUM([SalesAmount]), INDEX(1, , -1))

Enhance a table so it contains, for each month:
– the total sales amount;
– the difference to the first month of the respective year;
– and the difference to the first month of the respective quarter.

The screenshot below shows the visual matrix and the first visual calculation expression:

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/dax/index-function-dax