Ravindra BagaleCourses & study guides

13. DAX: Data Analysis Expressions

13.9 FILTER

By Last updated:

FILTER(<table>, <condition>) returns a table containing fakt the rows where the condition is true. It is an iterator. Use it when the condition involves a measure or complex logic across columns.

Loyal Customers =
COUNTROWS(
    FILTER(VALUES(Customer[Customer ID]), [Total Orders] >= 10)
)

Sales from Loyal Customers =
CALCULATE(
    [Total Sales],
    FILTER(VALUES(Customer[Customer ID]), [Total Orders] >= 10)
)

Slow Stores =
COUNTROWS(
    FILTER(VALUES(DarkStore[Store ID]), [Avg Delivery Time (mins)] > 15)
)

Do not FILTER a whole table when a column filter is enough

CALCULATE([Total Sales], FILTER(Orders, Orders[Discount] > 0)) works but iterates (एकेक ओळ फिरून हिशोब करणे) the entire fact table and can be slow. The simple form CALCULATE([Total Sales], Orders[Discount] > 0) filters fakt one column and is faster. Filter columns, not tables.

Ravindra Bagale's Tip

Friends, many students forget that FILTER returns a table, and try to use it on its own as a value in a measure. Wrap it in CALCULATE, COUNTROWS or an iterator. For conditions on a measure, filter a small column list: FILTER(VALUES(Customer[Customer ID]), [Total Sales] > 2000). This matters for both exams and interviews.