In 2023, we released the first draft of the Window functions in DAX whitepaper as part of SQLBI+. Since then, we have released a few updates and are now glad to announce the availability of the related 3-hour video course covering the full whitepaper content.

Window functions are an important extension of the DAX language to support visual calculations, also covered in another draft whitepaper, Understanding visual calculations in DAX. When visual calculations are generally available, we will finalize the whitepaper and release a related video course.

Here are the main topics covered in the video course:

• Introducing window functions
• Introducing INDEX
• Introducing PARTITIONBY
• Introducing “apply semantics”
• Introducing OFFSET
• Introducing WINDOW
• Understanding WINDOW and “apply semantics”
• Understanding “apply semantics“
• Introducing RANK and ROWNUMBER
• Duplicate rows in the source table
• Circular dependency in calculated columns
• Ambiguous row context
• Analyzing the performance of window functions
• Analyzing running totals
• Reducing materialization with window functions
• Comparing the first order versus the average of other orders

I want to personally thank all SQLBI+ subscribers for their support, which allows us to continue producing quality content for topics that despite being rather niche, are certainly useful for those who use DAX in their reports.

INDEX

Retrieves a row at an absolute position (specified by the position parameter) within the specified partition sorted by the specified order or on the axis specified.

`INDEX ( <Position> [, <Relation>] [, <OrderBy>] [, <Blanks>] [, <PartitionBy>] [, <MatchBy>] [, <Reset>] )`

PARTITIONBY

The columns used to determine how to partition the data. Can only be used within a Window function.

`PARTITIONBY ( [<PartitionBy_ColumnName> [, <PartitionBy_ColumnName> [, … ] ] ] )`

OFFSET

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.

`OFFSET ( <Delta> [, <Relation>] [, <OrderBy>] [, <Blanks>] [, <PartitionBy>] [, <MatchBy>] [, <Reset>] )`

WINDOW

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

`WINDOW ( <From> [, <FromType>], <To> [, <ToType>] [, <Relation>] [, <OrderBy>] [, <Blanks>] [, <PartitionBy>] [, <MatchBy>] [, <Reset>] )`

RANK

Returns the rank for the current context within the specified partition sorted by the specified order or on the axis specified.

`RANK ( [<Ties>] [, <Relation>] [, <OrderBy>] [, <Blanks>] [, <PartitionBy>] [, <MatchBy>] [, <Reset>] )`

ROWNUMBER

Returns the unique rank for the current context within the specified partition sorted by the specified order or on the axis specified.

`ROWNUMBER ( [<Relation>] [, <OrderBy>] [, <Blanks>] [, <PartitionBy>] [, <MatchBy>] [, <Reset>] )`