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

     <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


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”

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

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

Related DAX Functions


Related Course Modules