WINDOW DAX Function (Filter)

Syntax  |  Return values  |  Remarks  |  Related  

Retrieves a range of rows within the specified partition, sorted by the specified order or on the axis specified.

Syntax

WINDOW ( <From> [, <FromType>], <To> [, <ToType>] [, <Relation>] [, <OrderBy>] [, <Blanks>] [, <PartitionBy>] )
Parameter Attributes Description
From

Indicates where the window starts. It can be any DAX expression that returns a scalar value.
The behavior depends on the FromType parameter:

  • If FromType is REL, the number of rows to go back (negative value) or forward (positive value) from the current row to get the first row in the window.
  • If FromType is ABS, and From is positive, then it’s the position of the start of the window from beginning of the partition. Indexing is 1-based. For example, 1 means window starts from the beginning of the partition. If From is negative, then it’s the position of the start of the window from the end of the partition. -1 means the last row in the partition.
FromType Optional

Modifies behavior of the From parameter. Possible values are ABS (absolute) and REL (relative). Default is REL.
To

Indicates the end of the window. The last row is included in the window.
It can be any DAX expression that returns a scalar value.
The behavior depends on the ToType parameter:

  • If ToType is REL, the number of rows to go back (negative value) or forward (positive value) from the current row to get the last row in the window.
  • If ToType is ABS, and To is positive, then it’s the position of the end of the window from beginning of the partition. Indexing is 1-based. For example, 1 means window starts from the beginning of the partition. If To is negative, then it’s the position of the end of the window from the end of the partition. -1 means the last row in the partition.
ToType Optional

Modifies behavior of the To parameter. Possible values are ABS (absolute) and REL (relative). Default is REL.
Relation 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 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.

Return values

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

All rows from the window.

Remarks

Each <orderBy> and <partitionBy> column must have a corresponding outer value to help define the current row on which to operate. If <from_type> and <to_type> both have value ABS, then the following applies only to the <partitionBy> columns:

  • If there is exactly one corresponding outer column, its value is used.
  • If there is no corresponding outer column:
    • WINDOW will first determine all <orderBy> and <partitionBy> columns that have no corresponding outer column.
    • For every combination of existing values for these columns in WINDOW’s parent context, WINDOW is evaluated, and the corresponding rows is returned.
    • WINDOW final output is a union of these rows.
  • If there is more than one corresponding outer column, an error is returned.

If the columns specified within OrderBy and PartitionBy cannot uniquely identify every row in Relation, then:

  • WINDOW will try to find the least number of additional columns required to uniquely identify every row.
  • If such columns can be found, WINDOW 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 whole window is outside the partition, or the beginning of the window is after its ending.

If WINDOW is used within a calculated column defined on the same table as Relation, and OrderBy is omitted, an error is returned.

If the beginning of the window turns out be before the first row, then it’s set to the first row. Similarly, if the end of the window is after the last row of the partition, then it’s set to the last row.

Related functions

Other related functions are:

Last update: Jan 31, 2023   » Contribute   » Show contributors

Contributors: Alberto Ferrari, Marco Russo

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

2018-2023 © 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 WINDOW? 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.