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

To access our comprehensive DAX formula reference guide just insert your email address below. The resource will download immediately and we’ll also email it to you.