DAX Function Guide

FILTER
Empty image or helper icon

Sam McKay

CEO & Founder

How does the FILTER 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?

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

Loading

Considerations when using the FILTER?

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

Loading

Formula examples using the FILTER

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 Courses

Loading