Calculate across rows related to whichever row is current, e.g. rankings or running totals. DAX provides OFFSET, WINDOW, INDEX, RANK and ROWNUMBER, partitioned and ordered through PARTITIONBY and ORDERBY; T-SQL uses OVER, applied once GROUP BY and HAVING finish.
Read more: Microsoft Learn
In the Ultra Transcenders books
Each book explains Window functions in context, with comparison tables and the common traps.
Terms in this definition
- CALCULATE
A DAX function that computes an expression after changing the filter context with filter modifiers, Boolean filters or table filters. Called with no filters, it still turns any row context into filter context, which is known as context transition.
- RELATED
Fetches a column value from the lookup table, that is the one side of a many-to-one relationship, for whichever row is being evaluated. It therefore needs to run inside an iterator or a calculated column, where row context exists.
- DAX
Short for Data Analysis Expressions, the formula language you write measures, calculated columns and row-level security rules in, within tabular semantic models in Power BI, Excel Power Pivot or Analysis Services.
- OFFSET
A DAX window function that moves a given number of rows back (negative values) or forward (positive) from the current row in a sorted table, optionally split into partitions. It is commonly used to fetch the prior or next period's value.
- WINDOW
Returns rows from a sorted, optionally partitioned table, either at fixed positions (ABS) or relative to the current row (REL). Running totals plus moving averages are common uses.
- Index
Speeds up queries that filter on certain columns by keeping those columns sorted, with pointers back to each row; the price is more storage and slower writes.
- RANK
A window function, found in T-SQL (used with OVER) and in DAX, that gives each row's position within its partition. In DAX it accepts ORDERBY and PARTITIONBY, skips ranks after ties unless DENSE is chosen, and returns blank for total rows.
- ROWNUMBER
A DAX window function that gives the current row a unique position within a sorted, optionally partitioned table. Where the sort and partition columns leave a tie, it brings in more columns to settle it, and errors if there are none; RANK, by contrast, lets tied rows share a position.
Related terms
- MATCHBY
Used within DAX window functions such as OFFSET, WINDOW, INDEX, RANK and ROWNUMBER, this lists the columns that pin down which row is current when the data lacks a unique key.
- ORDERBY
Sets how rows are sorted within each partition. This DAX helper can only appear inside window functions like OFFSET, RANK or ROWNUMBER.
- PARTITIONBY
Splits the data into groups so that ranking or row navigation starts again in each one. This DAX helper is only valid within window functions.
- Ranking functions
The T-SQL window functions ROW_NUMBER, RANK, DENSE_RANK and NTILE, which assign numbers to rows in each partition according to the ORDER BY in their OVER clause.
- Serialized row set
A KQL row set with a guaranteed order, created by the serialize, sort or top operators and kept in order by operators like where, project and extend. Window functions including row_number(), row_cumsum(), prev() and next() can only run on such a row set.