DAX function FILTER functions (filtering functions) are used to get values from the table. Some functions enable additional filtering in a given table. These functions are often part of different functions as is nested parameter and enable dynamic calculations. They often serve as a value filter for function CALCULATE. Mostly it is the case of typical member of this category – FILTER function. For instance when we want to create metrics summing up values of some column but we need to limit the column based on some requirement = filter.
Script example representatives of DAX category function FILTER (function is named identically to the whole category). In the example of metrics is total sum of the sales > 1000
Sales USD > 1000 =
SUMX (
FILTER (
Sales,
Sales[UnitCosts] * Sales[Quantity]
> 1000
),
Sales[UnitPrice] * Sales[Quantity]
)
List of FILTER Functions in DAX Language for Power BI
- ADDCOLUMNS
- ADDMISSINGITEMS
- ALL
- ALLEXCEPT
- ALLNOBLANKROW
- ALLSELECTED
- CALCULATE
- CALCULATETABLE
- CALENDAR
- CALENDARAUTO
- CROSSFILTER
- CROSSJOIN
- CURRENTGROUP
- DISTINCT
- EARLIER
- EARLIEST
- EXCEPT
- FILTER
- FILTERS
- GENERATE
- GENERATEALL
- GROUPBY
- IGNORE
- INTERSECT
- ISONORAFTER
- KEEPFILTERS
- LOOKUPVALUE
- NATURALINNERJOIN
- NATURALLEFTOUTERJOIN
- RELATED
- RELATEDTABLE
- ROLLUP
- ROLLUPADDISSUBTOTAL
- ROLLUPGROUP
- ROLLUPISSUBTOTAL
- ROW
- SAMPLE
- SELECTCOLUMNS
- SUBSTITUTEWITHINDEX
- SUMMARIZE
- SUMMARIZECOLUMNS
- TOPN
- UNION
- USERELATIONSHIP
- VALUES