Open In App

Power BI- DAX Window Function

Improve
Improve
Like Article
Like
Save
Share
Report

The filter and value functions in DAX are some of the most complex and powerful and differ greatly from Excel functions. The lookup functions work by using tables and relationships, like a database. The filtering functions let you manipulate data context to create dynamic calculations. The WINDOW function is quite useful for calculations like a Moving Average or a Running Sum since it enables us to do computations based on ranges and get a slice of results using absolute or relative positioning.

Dataset

Here we are applying the WINDOW function on the Fact Internet Sales sample dataset. That caters to the sales being done through online channels as can be seen below in the screenshot:

Dataset

 

DAX Filter Window

This function provides a number of rows that are positioned within the specified range.

Syntax: WINDOW ( from[, from_type], to[, to_type][, <relation>][, <orderBy>][, <blanks>][, <partitionBy>] )

Parameters

from

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

– If <from_type> 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 <from_type> is ABS, and <from> is positive, then it’s the position of the start of the window from the beginning of the partition. Indexing is 1-based. For example, 1 means the 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.

from_type Changes how the from parameter behaves. ABS (absolute) and REL are two possible values (relative). REL is the default.
to The same as <from>, but with a window end indication. The window contains the final row.
to_type Similar to <from type> but changes <to> behavior.
relation

A table expression from which the output is retrieved (optional). If it is given, it must be the source of all columns in both orderBy and partitionBy. If left out:

-<orderBy> needs to be given explicitly.

-The <partitionBy> and <orderBy> columns must all originate from the same table.

-By default, ALLSELECTED () of all <orderBy> and <partitionBy> columns is used.

orderBy

An ORDERBY () clause is optionally included and contains the columns that specify how each partition is sorted. If unavailable:

-<relation> must be given explicitly.

-<OrdersBy> every relational column by default that is not previously set in <partitionBy>.

blanks (An enumeration that specifies how to deal with blank data when sorting is optional. This parameter has been set aside for potential use.
The only value that is currently available is KEEP (default), where blank values are arranged between zero and negative values for numerical and date variables. Blank values are sorted before all strings, including empty strings, in the case of strings.
partitionBy (Optional) A PARTITIONBY() clause lists the columns that specify how the partitioning of the relationship is defined.
If not available, <relation> is handled as a single division.

Output

Each row is toward the window.

Example: Window = CALCULATE(SUMX(FactInternetSales,FactInternetSales[SalesAmount]+FactInternetSales[TotalProductCost]),WINDOW(-2,REL,2,REL,ALLSELECTED(FactInternetSales[TotalProductCost]),ORDERBY(FactInternetSales[TotalProductCost])))

Window-function

 

Note:

To aid identify the current row on which to work, each “orderBy” and “partitionBy” column must have a matching outer value. Only the “partitionBy” columns are affected if “from type” and “to type” are both set to ABS:

  • Its value is used if there is exactly one equivalent outer column.
  • If there isn’t an equivalent outer column:
  • If there isn’t an outer column that corresponds, WINDOW will first identify all orderBy and partitionBy columns that don’t have an outer column that corresponds to them. WINDOW is assessed, and the related rows are returned, for each possible combination of the values currently in use for these columns in WINDOW’s parent context. The result from WINDOW is a union of these rows.
  • An error is displayed if there are multiple corresponding outer columns.

If each row in the relation can’t be uniquely identified by the columns supplied in the orderBy and partitionBy functions, then:

  • The least amount of additional columns needed to uniquely identify each row will be sought by WINDOW.
  • Each partition is sorted using this new set of orderBy columns if such columns can be located, which is done automatically by WINDOW.
  • In the absence of such columns, an error is returned.

If an empty table is given back.

  • An orderBy or partitionBy column’s matching outer value does not exist within a relation.
  • The beginning of the window is either after the end of the partition, or the entire window is outside of it.

An error is returned if WINDOW is used within a calculated column that is declared on the same database as “relation” and “orderBy” is left out.

The first row is assigned as the window’s starting point if it turns out to be before that row. Similar to this, it is set to the last row if the window’s end is after the partition’s last row.


Last Updated : 23 Jan, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads