Menu
×
   ❮   
HTML CSS JAVASCRIPT SQL PYTHON JAVA PHP C C++ C# AWS W3.CSS HOW TO BOOTSTRAP REACT MYSQL JQUERY EXCEL XML DJANGO NUMPY PANDAS NODEJS DSA TYPESCRIPT ANGULAR ANGULARJS GIT POSTGRESQL MONGODB ASP AI R GO KOTLIN SWIFT SASS VUE GEN AI SCIPY CYBERSECURITY DATA SCIENCE INTRO TO PROGRAMMING HTML & CSS BASH RUST TOOLS MATH

Power BI DAX FILTER


Share

The FILTER Function

FILTER goes through a table row by row, and keeps only the rows where a condition is true.

The result is a table, not a number.

Syntax

FILTER(table, filter)
ArgumentMeaning
tableThe table to filter, like Sales.
filterA condition that is true or false, checked for each row, like Sales[Quantity] >= 10.

A card cannot show a table.

That is why FILTER is used inside other functions that need a table, such as CALCULATE, COUNTROWS and SUMX.

This page uses the Poke Mart model described in DAX Introduction, with the Amount and Cost columns from DAX RELATED, and the measures Total Sales from DAX Measures and Order Count from DAX Aggregation Functions.


See the Table That FILTER Returns

To see what FILTER returns, you can save its result as a calculated table:

  1. Select Table view on the left edge of Power BI Desktop.
  2. On the Table tools tab, select New table.
  3. Type the formula below in the formula bar, and press Enter.

Example

Big Order Rows = FILTER(Sales, Sales[Quantity] >= 10)

A new table appears in the Data pane.

The first rows look like this (the Date column is not shown):

123OrderIDABCStoreIDABCProductID123Quantity123Amount123Cost
1002S2P01193,8002,090
1003S6P02137,8004,290
1005S4P04195,7002,850
1009S6P01112,2001,210
1011S1P01285,6003,080
1016S4P09102,000950
..................

The table has the same columns as Sales, but only 971 of the 2,740 rows: the orders with a quantity of 10 or more.

Order 1001 (quantity 1) and order 1004 (quantity 9) are not in it.

Note: A calculated table is calculated when the data is loaded or refreshed.

Slicers do not change it.

This table was only for looking, so delete it now: right-click Big Order Rows in the Data pane, select Delete from model, and select Yes in the Delete table box.


Count Rows with COUNTROWS and FILTER

COUNTROWS counts the rows of a table.

Together with FILTER, it counts the rows that meet a condition:

Example

Big Order Count = COUNTROWS(FILTER(Sales, Sales[Quantity] >= 10))

In a matrix by Category, next to Order Count:

CategoryOrder CountBig Order Count
Balls1,035556
Medicine1,110315
Status Healers595100
Total2,740971

Unlike the calculated table, this measure follows the filter context.

In the Balls row, FILTER only sees the 1,035 Ball orders, and 556 of them are big orders.

More than half of all Ball orders are for 10 or more balls.



CALCULATE with FILTER

FILTER can also be used as a filter in CALCULATE.

This measure shows the sales of the big orders only:

Example

Big Order Sales = CALCULATE([Total Sales], FILTER(Sales, Sales[Quantity] >= 10))
CategoryTotal SalesBig Order Sales
Balls4,447,2002,727,000
Medicine4,413,6001,494,000
Status Healers556,000131,100
Total9,416,8004,352,100

The big orders are 971 of the 2,740 orders, but they bring in 4,352,100 of the 9,416,800 in sales.

A Simpler Way for This Condition

The condition Sales[Quantity] >= 10 uses one column of one table.

It follows the rules for simple filters, so FILTER is not needed here.

You can write Big Order Sales like this:

Example

Big Order Sales = CALCULATE([Total Sales], Sales[Quantity] >= 10)

It returns the same results, and it is faster.

You will see why below.


When You Need FILTER

Use FILTER when a condition cannot be a simple filter.

The most common case is a condition on a measure.

Example: the sales of the best-selling products, the ones with Total Sales over 1,000,000:

Example

Top Product Sales = CALCULATE([Total Sales], FILTER(Products, [Total Sales] > 1000000))

FILTER goes through the 10 rows of the Products table.

For each product, it calculates Total Sales for that product, and keeps the product if the result is over 1,000,000. Four products pass: Poke Ball (1,821,600), Great Ball (1,677,600), Super Potion (1,545,600) and Potion (1,527,000).

Then CALCULATE adds up the sales of those four products.

CategoryTotal SalesTop Product Sales
Balls4,447,2003,499,200
Medicine4,413,6003,072,600
Status Healers556,000
Total9,416,8006,571,800

No Status Healer sells for more than 1,000,000, so that row is blank.

Note: The condition is checked in the current filter context.

In a matrix by Year, a product must sell for more than 1,000,000 in that year alone.

No product does in 2024, so the 2024 cell is blank.

In 2025, only Poke Ball does (1,018,400).

FILTER is also needed when one condition uses columns from two different tables, for example Quantity from the Sales table and Region from the Stores table.


Performance: Filter Columns, Not Tables

FILTER(Sales, ...) checks the condition for every row of the Sales table.

That is 2,740 rows here, and it can be millions in a large model.

A simple filter like Sales[Quantity] >= 10 only has to check the different values of one column.

The Quantity column has only 30 different values (1 to 30).

Power BI stores data by column, so this kind of filter is very fast.

Microsoft's advice:

  • Use simple column filters in CALCULATE whenever you can.
  • Use FILTER only when you need it: for conditions on measures, and for complex conditions.
  • When you need FILTER, filter a small table if you can, like Products (10 rows) rather than Sales.

What You Learned

  • FILTER returns a table with the rows where a condition is true.
  • COUNTROWS(FILTER(...)) counts the rows that meet a condition.
  • FILTER can be a filter in CALCULATE, and it is needed for conditions on measures.
  • For a simple condition on one column, a simple filter in CALCULATE is shorter and faster.


Free account

Track your progress

XP earned 0 Day streak 0 Spaces 0 League —

New

Learn in Adventure Mode W3Schools Adventure App

Coding fundamentals as bite-sized lessons and challenges.

×

Contact Sales

If you want to use W3Schools services as an educational institution, team or enterprise, send us an e-mail:
sales@w3schools.com

Report Error

If you want to report an error, or if you want to make a suggestion, send us an e-mail:
help@w3schools.com

W3Schools is optimized for learning and training. Examples might be simplified to improve reading and learning. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. While using W3Schools, you agree to have read and accepted our terms of use, cookies and privacy policy.

Copyright 1999-2026 by Refsnes Data. All Rights Reserved. W3Schools is Powered by W3.CSS.