FILTER

How to Use the Power BI DAX function FILTER

In this article, we will explain how to use the Power BI DAX function FILTER. We will cover the syntax of the function, its arguments, and examples of how to use it.

Syntax of the FILTER Function

The syntax of the FILTER function is as follows:

FILTER(❰Table❱, ❰FilterExpression❱)

- `❰Table❱`: The name of the table that you want to filter.

- `❰FilterExpression❱`: The expression that defines the filter criteria.

Arguments of the FILTER Function

The FILTER function has two arguments:

- `❰Table❱`: This argument specifies the table that you want to filter. It can be any table in the data model.

- `❰FilterExpression❱`: This argument specifies the expression that defines the filter criteria. It can include one or more conditions that must be met for the filter to be applied.

Examples of Using the FILTER Function

Let's take a look at some examples of how to use the FILTER function in Power BI.

Example 1: Filtering a Table by a Single Condition

Suppose you have a table called "Sales" that contains data on the sales of products. You want to filter this table to show only the sales that occurred in the year 2020. Here's how you can do it using the FILTER function:

FilteredTable = FILTER(Sales, Sales[Year] = 2020)

In this example, `Sales` is the name of the table we want to filter, and `Sales[Year] = 2020` is the filter expression. This expression tells Power BI to filter the `Sales` table and only show the rows where the value in the "Year" column is equal to 2020. The filtered table is assigned to a new table called `FilteredTable`.

Example 2: Filtering a Table by Multiple Conditions

Suppose you have a table called "Sales" that contains data on the sales of products. You want to filter this table to show only the sales that occurred in the year 2020 and that were made by the salesperson "John". Here's how you can do it using the FILTER function:

FilteredTable = FILTER(Sales, Sales[Year] = 2020 && Sales[Salesperson] = “John”)

In this example, `Sales` is the name of the table we want to filter, and `Sales[Year] = 2020 && Sales[Salesperson] = "John"` is the filter expression. This expression tells Power BI to filter the `Sales` table and only show the rows where the value in the "Year" column is equal to 2020 and the value in the "Salesperson" column is equal to "John". The filtered table is assigned to a new table called `FilteredTable`.

Example 3: Using the FILTER Function in Calculated Columns

The FILTER function can also be used in calculated columns to create new columns in a table that are based on a filter expression. Suppose you have a table called "Sales" that contains data on the sales of products. You want to create a new column that shows the sales for each product that occurred in the year 2020. Here's how you can do it using the FILTER function:

Sales2020 = CALCULATE(SUM(Sales[SalesAmount]), FILTER(Sales, Sales[Year] = 2020))

In this example, `Sales[SalesAmount]` is the column we want to sum, and `FILTER(Sales, Sales[Year] = 2020)` is the filter expression. This expression tells Power BI to filter the `Sales` table and only include the rows where the value in the "Year" column is equal to 2020. The `SUM` function is used to sum the values in the `SalesAmount` column for the filtered rows. The calculated column is assigned to a new column called `Sales2020`.

The Power BI DAX function FILTER is a powerful tool for filtering data based on specific criteria. It allows users to analyze and visualize their data more effectively, making it easier to make informed decisions. By understanding the syntax and arguments of the FILTER function, you can take full advantage of its capabilities and unlock the full potential of Power BI.

Power BI DAX Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)