Filter window function results with the QUALIFY clause in Fabric

The QUALIFY clause lets you filter rows based on window or analytic functions directly, removing the need for subqueries or CTEs to get the desired dataset.

Use cases

Imagine you need to retrieve the highest-priced product for each category in a sales table without resorting to complex nested structures.

• Filter top-N per group directly.
• Apply conditions on moving averages or running totals computed by windows.
• Limit results based on rankings (RANK or ROW_NUMBER).

How to use

Add the QUALIFY clause after WHERE, GROUP BY, and HAVING in your SQL statement.

SELECT *
FROM (VALUES ('Red',    'Road Bike',     1200.00 ),
             ('Red',    'City Bike',     800.00  ),
             ('Blue',   'Mountain Bike', 1500.00 ),
             ('Blue',   'Road Bike',     1100.00 )) AS
      Source ( Color,    Product,        Price   )
QUALIFY
        ROW_NUMBER() OVER (PARTITION BY Color ORDER BY Price DESC) = 1;

What happens in this code:

1. The ROW_NUMBER() function value is computed for each row partitioned by product color.
2. Rows are ordered descending by price within each color partition.
3. The QUALIFY clause filters the dataset to return only the row with the top position in each partition.

Limitations and requirements

• Feature in Preview.
• Available in Warehouse and SQL analytics endpoint in Microsoft Fabric.
• The QUALIFY clause requires at least one window function in its predicate or in referenced aliases.
• Not supported directly in UPDATE and DELETE statements.

Using it in a pipeline

Use QUALIFY in the Silver or Gold layer to clean and refine complex analytical datasets smoothly before loading final tables for reporting consumption.