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.
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.
A table expression from which the output row 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.
One or more rows from Relation.
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.
Other related functions are:
Contributors: Alberto Ferrari, Marco Russo
Microsoft documentation: https://learn.microsoft.com/en-us/dax/offset-function-dax