Press "Enter" to skip to content

Window Functions and DAX

Marco Russo and Alberto Ferrari explain how window functions work in DAX:

Window functions like OFFSET and WINDOW return rows from a table based on the current row. For example, OFFSET (-1) returns the previous row. The main question is: how does OFFSET determine the current row? Intuitively, it searches in the row context and in the filter context for values of columns, and it determines the current row. Unfortunately, that intuitive understanding is missing many details. Although it is easy to intuit the current row in simple queries, things are sometimes more complex. To make sense of the current row in a non-trivial scenario, you need to understand apply semantics.

Read on to learn more, especially when there are partition groups (think the PARTITION BY section in a T-SQL window clause).