A
C
- CALCULATE
- CALCULATETABLE
- CALENDAR
- CALENDARAUTO
- CEILING
- CHISQ.DIST
- CHISQ.DIST.RT
- CHISQ.INV
- CHISQ.INV.RT
- CLOSINGBALANCEMONTH
- CLOSINGBALANCEQUARTER
- CLOSINGBALANCEYEAR
- COALESCE
- COLUMNSTATISTICS
- COMBIN
- COMBINA
- COMBINEVALUES
- CONCATENATE
- CONCATENATEX
- CONFIDENCE.NORM
- CONFIDENCE.T
- CONTAINS
- CONTAINSROW
- CONTAINSSTRING
- CONTAINSSTRINGEXACT
- CONVERT
- COS
- COSH
- COT
- COTH
- COUNT
- COUNTA
- COUNTAX
- COUNTBLANK
- COUNTROWS
- COUNTX
- COUPDAYBS
- COUPDAYS
- COUPDAYSNC
- COUPNCD
- COUPNUM
- COUPPCD
- CROSSFILTER
- CROSSJOIN
- CUMIPMT
- CUMPRINC
- CURRENCY
- CURRENTGROUP
- CUSTOMDATA
D
E
I
N
O
P
R
S
- SAMEPERIODLASTYEAR
- SAMPLE
- SEARCH
- SECOND
- SELECTCOLUMNS
- SELECTEDMEASURE
- SELECTEDMEASUREFORMATSTRING
- SELECTEDMEASURENAME
- SELECTEDVALUE
- SIGN
- SIN
- SINH
- SLN
- SQRT
- SQRTPI
- STARTOFMONTH
- STARTOFQUARTER
- STARTOFYEAR
- STDEVX.P
- STDEVX.S
- STDEV.P
- STDEV.S
- SUBSTITUTE
- SUBSTITUTEWITHINDEX
- SUM
- SUMMARIZE
- SUMMARIZECOLUMNS
- SUMX
- SWITCH
- SYD
T
U
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.