Power BI- DAX Index Function

Data filtering is one of the most widely used data analysis techniques. Data filtering eliminates the clutter and makes important information visible to everyone, whether they are financial experts, data scientists, marketers, or salespeople. Filtered data often results in a subset of data that may be used to create future studies. Unquestionably, Power BI, the most popular and well-known business intelligence product of our day, offers data filtering through the use of DAX filter capabilities. The DAX filter function is an iterator function that generates filtered tables for your data models. It is a part of the DAX function library. Each row that satisfies your condition is returned as output.

DAX Filter Index

The DAX filter functions are among the most powerful and sophisticated, and they differ greatly from Excel operations. The lookup functions use tables and relationships to act similarly to a database. By using filtering functions to change the context of the input, dynamic calculations can be produced.

DAX Filter Index gives back a row sorted by the supplied order, at the position parameter’s specified absolute position within the specified partition. Multiple rows may be returned if the present partition cannot be reduced to a single partition. Below is the syntax for the index function:

Syntax: INDEX(<position>[, <relation>][, <orderBy>][, <blanks>][, <partitionBy>])

Dataset

Let’s practice the DAX Filter Index Function on the DimDate Date type dataset of a reseller. The screenshot of the dataset is given below:

Parameters

position	The data’s absolute position (1-based) starting point: – The position is favorable: The first row starts with 1, the second row with 2, etc. – The stance is adverse: The last row is indicated by a -1, the second-last row by a -2, etc. An empty table will be returned by INDEX when position> is outside the bounds, zero, or BLANK (). Any DAX expression that yields a scalar value is acceptable.
relation	A table expression from which the output is retrieved (optional). If it is given, it must be the source of all columns in both orderBy and partitionBy. If left out: – <orderBy> needs to be given explicitly. – The <partitionBy> and <orderBy> columns must all originate from the same table. – By default, ALLSELECTED () of all <orderBy> and <partitionBy> columns is used.
orderBy	(Optional) an ORDERBY() clause with the columns used to specify the order of each split. If left out: – <relation> must be given explicitly. – By every column in the <relation> that isn’t already given in <partitionBy> by default.
blanks	An enumeration that specifies how to deal with empty values when sorting. This parameter has been set aside for potential use. The only value that is currently available is KEEP (default), where blank values are arranged between zero and negative values for numerical and date variables. Blank values are sorted before all strings, including empty strings, in the case of strings.
partitionBy	(Optional) A PARTITIONBY() clause lists the columns that specify how the partitioning of the relationship is defined. The relation is handled as a single partition if it is missing.

Output

A line in a fixed position. Go to New Table and enter the following query:

Example: Table = INDEX(1,ALL(DimDate[EnglishMonthName]))

This will create a Table in the model.

Note:

To assist designate the “current partition” on which to work, each “partitionBy” column must have a matching outer value. This behavior is:

• Its value is used if there is exactly one equivalent outer column.
• If there isn’t an equivalent outer column:

All <partitionBy> columns without an outer column counterpart are initially identified using INDEX. The parent context of INDEX is evaluated, and a row is returned for each possible combination of the values that currently exist for these columns. The result of INDEX is a union of these rows.

• An error is displayed if there are multiple corresponding outer columns.

If each row in the relationship cannot be uniquely identified by the columns supplied in the orderBy and partitionBy clauses:

• The least number of additional columns needed to uniquely identify each row will be sought by INDEX.
• In the event that such columns are discovered, INDEX will automatically add these additional columns to orderBy>, and each partition will be sorted using this new set of OrderBy columns.
• In the absence of such columns, an error is returned.

A blank table is produced if:

• There is no such thing as a PartitionBy column’s outer value in the relation.
• The value for “position” refers to a position outside of the partition.

An error is returned if INDEX is used within a calculated column defined on the same database as “relation” and “orderBy” is left out.