This whitepaper is a draft.
The functionalities described are currently in preview and are subject to change.
We will update the whitepaper after these functions become generally available.

INDEX, OFFSET, and WINDOW are new table functions that aim to navigate over a sorted and partitioned table to obtain both absolute and relative rows. The main purpose of window functions is to make it easier to perform calculations like:

  • Sorting products by sales amount and comparing the sales of the current product against the previous product.
  • Comparing sales in the current month against the previous month.
  • Finding the difference in the net price of a product between one sale and the previous sale.
  • Computing moving averages, determining a window of n months or days.

Due to their nature, these functions are more likely to be used in queries rather than measures. We introduce most functions using EVALUATE statements because it makes understanding the functions’ returned values and behavior easier. The window functions can also be used in measures.

Window functions are unique in the way they offer sorting and partitioning of the input table through the helper functions ORDERBY and PARTITIONBY. We describe the behavior of ORDERBY and PARTITIONBY when talking about the INDEX function. The same considerations are valid for the other functions, and we will not repeat them.

Moreover, window functions are the first DAX functions that implement “apply semantics”. “Apply semantics” is a new concept in DAX. Understanding this peculiarity is paramount to obtaining the best out of window functions and making sense of complex code.

The content is organized into the following modules:

  • Introducing window functions
  • Introducing INDEX
    • Handling ties
    • Introducing blank handling
    • Introducing PARTITIONBY
    • Omitting the source table
  • Enjoying a small digression
  • Introducing “apply semantics”
  • Introducing OFFSET
  • Introducing WINDOW
    • Understanding WINDOW and “apply semantics”
  • Understanding “apply semantics”
    • Matching
    • Applying
    • Pseudo DAX algorithm
    • Apply semantics example
    • Introducing MATCH BY
  • Introducing RANK and ROWNUMBER
  • Common window function errors
    • Duplicate rows in 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
  • Conclusions

Additional content: Sample file used for the demos in the whitepaper.