FILTERS DAX Function (Table manipulation)
Returns a table of the filter values applied directly to the specified column.
Syntax
Parameter | Attributes | Description |
---|---|---|
ColumnName |
The column for which filter values are to be returned. |
Return values
A column of unique values.
Remarks
When FILTERS is evaluated in an expression grouped in SUMMARIZECOLUMNS the original filter could be lost and replaced by the result of the auto-exists behavior that combines all the filters on the same table into a single filter. The combined table resulting from this filter only contains columns explicitly listed in SUMMARIZECOLUMNS as grouping columns or filter columns.
Examples
-- FILTER returns the filters directly applied to a column EVALUATE CALCULATETABLE ( FILTERS ( 'Product'[Category] ), 'Product'[Category] = "Audio" ) EVALUATE CALCULATETABLE ( FILTERS ( 'Product'[Category] ), 'Product'[Category] IN { "Audio", "Computers" } )
Category |
---|
Audio |
Category |
---|
Audio |
Computers |
-- Non-existing values are not considered as filters -- The "Bananas" category does not exist in Product table EVALUATE CALCULATETABLE ( FILTERS ( 'Product'[Category] ), 'Product'[Category] IN { "Audio", "Bananas" } )
Category |
---|
Audio |
-- Filter on one column does not affect other columns, -- which are cross-filtered but not filtered. -- Only "Cameras and camcorders" has products with Azure color. EVALUATE CALCULATETABLE ( FILTERS ( 'Product'[Category] ), 'Product'[Color] = "Azure" ) EVALUATE CALCULATETABLE ( VALUES ( 'Product'[Category] ), 'Product'[Color] = "Azure" ) -- Table filter include columns used in FILTERS EVALUATE CALCULATETABLE ( FILTERS ( 'Product'[Category] ), FILTER ( 'Product', 'Product'[Color] = "Azure" ) )
Category |
---|
Audio |
TV and Video |
Computers |
Cameras and camcorders |
Cell phones |
Music, Movies and Audio Books |
Games and Toys |
Home Appliances |
Category |
---|
Cameras and camcorders |
Category |
---|
Cameras and camcorders |
-- FILTERS returns its values even though there are no -- rows satisfying the set of conditions. -- The table returned by FILTERS has the correct lineage. EVALUATE CALCULATETABLE ( ADDCOLUMNS ( FILTERS ( 'Product'[Color] ), "#Prods", CALCULATE ( COUNTROWS ( 'Product' ) ) ), 'Product'[Color] IN { "White", "Azure" }, 'Product'[Brand] = "Litware" )
Color | #Prods |
---|---|
White | 45 |
Azure | (Blank) |
Related articles
Learn more about FILTERS in the following articles:
-
Displaying filter context in Power BI Tooltips
This article describes how to display the filter context applied to a calculation using a special DAX measure in Power BI Tooltips. » Read more
Last update: Nov 7, 2024 » Contribute » Show contributors
Contributors: Alberto Ferrari, Marco Russo
Microsoft documentation: https://docs.microsoft.com/en-us/dax/filters-function-dax