FILTER Function (DAX)

How does the FILTER function (DAX) work?

The FILTER function (DAX) is one of Power BI’s most important functions. It returns a table containing a subset of a table or expression.

FILTER Formula Syntax

FILTER(
     <table> , <filter>
)

How do you use the FILTER function?

You can use FILTER to reduce the number of rows in the table you are working with and use only specific data for calculation purposes. FILTER is not used independently, but as a function embedded as a parameter in other functions that require a table, like CALCULATE for example.

Related Blog Posts

Related Support Forum Posts

FILTER Function Inside CALCULATE
Filter Only Display Last 3 Financial Years
Results The FILTER function Calculation Seems Off

Considerations when using the FILTER function

One of the advantages of FILTER is that everything is achieved in memory without any new columns of data being created in your tables. The FILTER function basically creates a new filtered copy of a table based on the logic you place inside it. All virtual tables such as this generated in DAX must maintain an active relationship with the rest of your model’s tables. This is called the lineage of the data. 

Related Video Tutorials

Formula examples using the FILTER function

Customer Sales = CALCULATE (
      SUM(‘Sales Data’ [Net Amount]),
      FILTER (
         ALL (‘Sales Data’ [Account Type]),
         ‘Sales Data'[Account Type] <> “REVENUE”
      )
)

SUMX(
    FILTER( table1;table1[date] <=
      MIN( dates_min[date]
      )
    ); table1[A]
)

CALCULATE (
    [Total Sales],
    FILTER (
      CALCULATETABLE ( stores, ALL ( stores ) ),
      stores[Area Manager] = AMSelected
    )
)

Related DAX Functions

Related Course Modules

Ultimate-Beginners-Guide-To-PowerBI

DOWNLOAD


Download our comprehensive DAX Formula Reference Guide as perfect companion as you learn how to use DAX formulas within Power BI.


Download DAX Formulas Reference Guide
Download DAX Formula Reference Guide