This month we published a second draft of the Window functions in DAX whitepaper for the SQLBI+ subscribers. After the first draft we published in January 2023, we added new sections and updated others.
We completely rewrote the “apply semantics” part, where you can find additional examples along with the description of the new MATCHBY function. This second draft also covers two other new functions: RANK and ROWNUMBER. We also updated the section about OFFSET, circular dependency, and duplicated rows.
You can find a note about new and updated sections in the table of contents, making it easier for you to focus on the parts that have changed, if you have already read the first draft.
The columns used to determine how to match data and identify the current row. Can only be used within a Window function.
MATCHBY ( [<MatchBy_ColumnName> [, <MatchBy_ColumnName> [, … ] ] ] )
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>] )
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>] )
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>] )