FILTERS
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
What is FILTERS?
FILTERS is a DAX function that allows users to filter data based on specific conditions. It is commonly used in creating custom measures and calculations, and it can be very useful for creating dynamic reports.
The syntax for the FILTERS function is as follows:
FILTERS(Table[Column], Condition1, Condition2, …, ConditionN)
- `Table[Column]`: The table and column to be filtered.
- `Condition1, Condition2, …, ConditionN`: The conditions to be applied to the filter.
How to Use FILTERS
To demonstrate how FILTERS works, let's take a look at an example. Suppose we have a sales data table with the following columns:
- Date
- Product
- Sales Amount
We want to create a report that shows the sales amount for the selected products. Here's how we can use FILTERS to achieve this:
1. Create a new measure by clicking on the "New Measure" button in the "Modeling" tab.
2. Enter the following formula for the measure:
Sales Amount Selected Products =
SUMX(
FILTERS(
Sales,
Sales[Product] = “Product 1” || Sales[Product] = “Product 2”
),
Sales[Sales Amount]
)
- `SUMX`: A DAX function that returns the sum of an expression evaluated for each row in a table.
- `FILTERS`: The FILTERS function is used to filter the Sales table based on the conditions specified in the second argument.
- `Sales[Product] = "Product 1" || Sales[Product] = "Product 2"`: This is the condition used to filter the Sales table. It says to only include rows where the product is "Product 1" or "Product 2".
- `Sales[Sales Amount]`: This is the column that we want to sum up.
3. Once the measure is created, we can use it in our report. Drag the "Product" column to the "Visualizations" area, and then drag the "Sales Amount Selected Products" measure to the "Values" area.
4. Now we can select the products we want to include in the report, and the measure will dynamically adjust based on the selected products.
In conclusion, the FILTERS function is a powerful tool that can be used to filter data based on specific conditions. It is a valuable addition to any Power BI user's toolbox and can be used to create dynamic and informative reports. By following the steps outlined in this article, you should be able to use the FILTERS function to create measures and calculations that filter data based on your specific needs.
