OFFSET DAX Function (Filter)
Retrieves a single row from a relation by moving a number of rows within the specified partition, sorted by the specified order or on the axis specified.
Syntax
| Parameter | Attributes | Description |
|---|---|---|
| Delta |
The number of rows before (negative value) or after (positive value) the current row from which to obtain the data. It can be any DAX expression that returns a scalar value. |
|
|
Relation Iterator |
Optional |
A table expression from which the output row is returned.
|
|
OrderBy Row Context |
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
One or more rows from Relation.
Remarks
Each OrderBy and PartitionBy column must have a corresponding outer value to help define the current row 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, then:
- OFFSET will first determine all OrderBy and PartitionBy columns that have no corresponding outer column.
- For every combination of existing values for these columns in OFFSET’s parent context, OFFSET is evaluated and a row is returned.
- OFFSET’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 can’t uniquely identify every row in Relation, then:
- OFFSET will try to find the least number of additional columns required to uniquely identify every row.
- If such columns can be found, OFFSET 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 an OrderBy or PartitionBy column does not exist within Relation.
- The Delta value causes a shift to a row that does not exist within the partition.
If OFFSET 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: Feb 2, 2023 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo
Microsoft documentation: https://learn.microsoft.com/en-us/dax/offset-function-dax