The FILTER() Function: Dynamic Row-by-Row Filtering

Overview

The FILTER function is an iterator function. Unlike standard filters that work instantly on an entire column, FILTER goes through your data table row-by-row to scan and check if each row meets your specific condition.

Because it evaluates data row-by-row, it is more powerful and can handle complex logic (like comparing two different columns or checking multiple conditions). However, it requires more processing power.

Syntax

DAX Syntax

FILTER(<table>, <filter_expression>)

<table>: The table you want to scan row-by-row.
<filter_expression>: The true/false condition you want to test on every single row.

Example & Dataset

Imagine you have a product stock table named Products containing data about current stock levels and safety stock margins:

Sample Data Table: Products

ProductID	ProductName	CurrentStock	SafetyStock	Price
P01	Laptop	15	10	1000
P02	Smartphone	5	8	500
P03	Headphones	50	20	100
P04	Smartwatch	3	5	250

We want to calculate total sales value only for items that are currently running Out of Stock (where CurrentStock is strictly less than SafetyStock). Since we need to compare two columns row-by-row, we must use FILTER() wrapped inside CALCULATE:

The DAX Code

Low Stock Value Measure

Low Stock Value =
CALCULATE(
    SUM(Products[Price]),
    FILTER(
        Products,
        Products[CurrentStock] < Products[SafetyStock]
    )
)

Expected Output Visual

When you drag ProductName and [Low Stock Value] into a Table Visual:

Output Table Visual

ProductName	Price (Standard)	Low Stock Value
Laptop	1000	BLANK (Stock 15 > Safety 10)
Smartphone	500	500 (Stock 5 < Safety 8)
Headphones	100	BLANK (Stock 50 > Safety 20)
Smartwatch	250	250 (Stock 3 < Safety 5)
Total	1850	750

Why Did This Output Happen?

The FILTER function scanned the table row-by-row. For "Laptop", 15 < 10 is False, so it returned BLANK. For "Smartphone", 5 < 8 is True, so CALCULATE summed its price ($500).