OFFSET DAX Function (Filter)

Syntax  |  Return values  |  Remarks  |  Examples  |  Articles  |  Related  

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

OFFSET ( <Delta> [, <Relation>] [, <OrderBy>] [, <Blanks>] [, <PartitionBy>] [, <MatchBy>] [, <Reset>] )
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.
If specified, all columns in OrderBy and PartitionBy must come from it.
If omitted:

  • OrderBy must be explicitly specified.
  • All OrderBy and PartitionBy columns must be fully qualified and come from a single table.
  • Defaults to ALLSELECTED() of all columns in OrderBy and PartitionBy.
OrderBy
Row Context 		Optional

An ORDERBY() clause containing the columns that define how each partition is sorted.
If omitted:

  • Relation must be explicitly specified.
  • Defaults to ordering by every column in Relation that is not already specified in PartitionBy.
Blanks Optional

An enumeration that defines how to handle blank values when sorting.
This parameter is reserved for future use.
Currently, the only supported value is KEEP (default), where the behavior for numerical/date values is blank values are ordered between zero and negative values. The behavior for strings is blank values are ordered before all strings, including empty strings.
PartitionBy Optional

A PARTITIONBY() clause containing the columns that define how Relation is partitioned.
If omitted, Relation is treated as a single partition.
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

Table An entire table or a table with one or more columns.

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.

» 5 related articles
» 4 related functions

Examples

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
DEFINE
    MEASURE Sales[Prev Year Sales] =
        CALCULATE (
            [Sales Amount],
            OFFSET (
                -1,
                ALLSELECTED ( 'Date'[Year] ),
                ORDERBY ( 'Date'[Year], ASC )
            )
        )
EVALUATE
SUMMARIZECOLUMNS (
    'Date'[Year],
    TREATAS ( { 2019, 2020 }, 'Date'[Year] ),
    "Sales Amount", [Sales Amount],
    "Prev Year Sales", [Prev Year Sales]
)
Copy Conventions#1
Date[Year] Sales Amount Prev Year Sales
2,019 3,550,194.76 (Blank)
2,020 769,835.80 3,550,194.76
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
DEFINE
    TABLE ProcessData =
        -- Create a table with index column and "process" values from the DAX Guide db
        SELECTCOLUMNS (
            SUMMARIZECOLUMNS (
                'Date'[Calendar Year Month Number],
                FILTER (
                    ALL ( 'Date'[Calendar Year Month Number] ),
                    'Date'[Calendar Year Month Number] < 200804
                ),
                "Amt", [Sales Amount]
            ),
            "Index", [Calendar Year Month Number],
            "Value", [Amt]
        )
    -- Basic sum measure of the value column
    MEASURE ProcessData[SumValue] =
        SUM ( ProcessData[Value] )
    -- Average value of the data
    VAR MeanValue =
        AVERAGE ( ProcessData[Value] )
    -- Use the OFFSET function to first calculate the absolute differences
    -- between two consequent values (a.k.a. Moving Range)
    -- https://en.wikipedia.org/wiki/Shewhart_individuals_control_chart
    MEASURE ProcessData[MovingRange] =
        VAR PreviousValue =
            CALCULATE (
                [SumValue],
                OFFSET (
                    -1,
                    ORDERBY ( ProcessData[Index] )
                )
            )
        RETURN
            IF (
                NOT ISBLANK ( PreviousValue ),
                ABS ( [SumValue] - PreviousValue )
            )
    -- Derive process standard deviation or "sigma" based on the Moving Range
    VAR Sigma =
        VAR MeanMovingRange =
            CALCULATE (
                AVERAGEX (
                    ALL ( ProcessData[Index] ),
                    [MovingRange]
                )
            )
        RETURN
            MeanMovingRange
                * SQRTPI ( 1 ) / 2
    -- Use the WINDOW function to implement the WE Rule 2 for process instability detection:
    -- "Two out of three consecutive points fall beyond the 2σ-limit (in zone A or beyond),
    -- on the same side of the centerline"
    -- https://en.wikipedia.org/wiki/Western_Electric_rules
    MEASURE ProcessData[Rule2Test] =
        VAR ZoneARowsPositive =
            CALCULATE (
                COUNTROWS ( ProcessData ),
                FILTER (
                    WINDOW (
                        -2,
                        0,
                        ORDERBY ( ProcessData[Index] )
                    ),
                    [SumValue] >= MeanValue + 2 * Sigma
                )
            )
        VAR ZoneARowsNegative =
            CALCULATE (
                COUNTROWS ( ProcessData ),
                FILTER (
                    WINDOW (
                        -2,
                        0,
                        ORDERBY ( ProcessData[Index] )
                    ),
                    [SumValue] <= MeanValue - 2 * Sigma
                )
            )
        RETURN
            -- Flag the rows where the rule is violated
            IF (
                ZoneARowsPositive >= 2 || ZoneARowsNegative >= 2,
                1
            )
 
EVALUATE
SUMMARIZECOLUMNS (
    ProcessData[Index],
    "SumValue", [SumValue],
    "Process Mean", MeanValue,
    "-2 σ", MeanValue - 2 * Sigma,
    "+2 σ", MeanValue + 2 * Sigma,
    "Rule 2 Indicator", [Rule2Test]
)
Copy Conventions#2
Index SumValue Process Mean -2 σ +2 σ Rule 2 Indicator
200,701 794,248.24 875,088.76 685,032.06 1,065,145.45 (Blank)
200,702 891,135.91 875,088.76 685,032.06 1,065,145.45 (Blank)
200,703 961,289.24 875,088.76 685,032.06 1,065,145.45 (Blank)
200,704 1,128,104.82 875,088.76 685,032.06 1,065,145.45 (Blank)
200,705 936,192.74 875,088.76 685,032.06 1,065,145.45 (Blank)
200,706 982,304.46 875,088.76 685,032.06 1,065,145.45 (Blank)
200,707 922,542.98 875,088.76 685,032.06 1,065,145.45 (Blank)
200,708 952,834.59 875,088.76 685,032.06 1,065,145.45 (Blank)
200,709 1,009,868.98 875,088.76 685,032.06 1,065,145.45 (Blank)
200,710 914,273.54 875,088.76 685,032.06 1,065,145.45 (Blank)
200,711 825,601.87 875,088.76 685,032.06 1,065,145.45 (Blank)
200,712 991,548.75 875,088.76 685,032.06 1,065,145.45 (Blank)
200,801 656,766.69 875,088.76 685,032.06 1,065,145.45 (Blank)
200,802 600,080.00 875,088.76 685,032.06 1,065,145.45 1
200,803 559,538.52 875,088.76 685,032.06 1,065,145.45 1

Related articles

Learn more about OFFSET in the following articles:

Related functions

Other related functions are:

Last update: Apr 15, 2025   » Contribute   » Show contributors

Contributors: Alberto Ferrari, Marco Russo, Ville-Pietari Louhiala

Microsoft documentation: https://learn.microsoft.com/en-us/dax/offset-function-dax

2018-2025 © SQLBI. All rights are reserved. Information coming from Microsoft documentation is property of Microsoft Corp. » Contact us   » Privacy Policy & Cookies

Context Transition

This function performs a Context Transition if called in a Row Context. Click to read more.

Row Context

This expression is executed in a Row Context. Click to read more.

Iterator

Not recommended

The use of this function is not recommended. See Remarks and Related functions for alternatives.

Not recommended

The use of this parameter is not recommended.

Deprecated

This function is deprecated. Jump to the Alternatives section to see the function to use.

Volatile

A volatile function may return a different result every time you call it, even if you provide the same arguments. Click to read more.

Deprecated

This parameter is deprecated and its use is not recommended.

DirectQuery compatibility

Limitations are placed on DAX expressions allowed in measures and calculated columns.
The state below shows the DirectQuery compatibility of the DAX function.

Contribute

Want to improve the content of OFFSET? Did you find any issue?
Please, report it us! All submissions will be evaluated for possible updates of the content.


This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

DAX Conventions