WINDOW DAX Function (Filter)
Retrieves a range of rows within the specified partition, sorted by the specified order or on the axis specified.
Syntax
Parameter | Attributes | Description |
---|---|---|
From |
Indicates where the window starts. It can be any DAX expression that returns a scalar value.
|
|
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.
|
|
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.
|
OrderBy | 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. If omitted, Relation is treated as a single partition. |
Return values
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