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 guide you on how to use the Power BI DAX function KEEPFILTERS in your reports and dashboards.
What is KEEPFILTERS?
Before we dive into the details of how to use KEEPFILTERS, let’s understand what it is. KEEPFILTERS is a DAX function that allows users to create a new table with filtered data. This function retains the existing filters from the original table and applies them to the new table. By doing so, KEEPFILTERS ensures that the data used in calculations is not affected by any changes made to the original filters.
How to Use KEEPFILTERS
To use the KEEPFILTERS function, you need to follow these steps:
1. Open Power BI and navigate to the “Data” tab.
2. Click on “New Measure” in the “Calculations” group.
3. Give a suitable name for your new measure and click on the “Advanced editor” option.
4. In the advanced editor, type the following formula:
Measure Name = CALCULATE ( [Aggregate Function], KEEPFILTERS ( Table[Column] ) )
5. Replace "Measure Name" with a name of your choice, "Aggregate Function" with the function you want to use (e.g., SUM, AVERAGE, etc.), "Table" with the name of the table you want to use, and "Column" with the name of the column you want to filter.
6. Once you have entered the formula, click on "OK" to save the measure.
Examples of KEEPFILTERS
Here are some examples of how to use the KEEPFILTERS function in Power BI:
Example 1: Sum of Sales for Selected Regions
Suppose you want to calculate the total sales for selected regions without affecting the original filters. You can use the KEEPFILTERS function to achieve this. Here's how:
1. Navigate to the "Data" tab and click on "New Measure."
2. Give a suitable name for your new measure (e.g., Total Sales for Selected Regions) and click on the "Advanced editor" option.
3. In the advanced editor, type the following formula:
Total Sales for Selected Regions = CALCULATE ( SUM ( Sales[Sales Amount] ), KEEPFILTERS ( Geography[Region] ) )
4. Once you have entered the formula, click on "OK" to save the measure.
5. Now, you can use the "Total Sales for Selected Regions" measure in your report or dashboard.
Example 2: Average Sales for Selected Products
Suppose you want to calculate the average sales for selected products without affecting the original filters. You can use the KEEPFILTERS function to achieve this. Here's how:
1. Navigate to the "Data" tab and click on "New Measure."
2. Give a suitable name for your new measure (e.g., Average Sales for Selected Products) and click on the "Advanced editor" option.
3. In the advanced editor, type the following formula:
Average Sales for Selected Products = CALCULATE ( AVERAGE ( Sales[Sales Amount] ), KEEPFILTERS ( Product[Product Name] ) )
4. Once you have entered the formula, click on "OK" to save the measure.
5. Now, you can use the "Average Sales for Selected Products" measure in your report or dashboard.
The KEEPFILTERS function is a powerful tool in Power BI that allows users to create a new table with filtered data. This function ensures that the data used in calculations is not affected by any changes made to the original filters. By following the steps outlined in this article, you can use the KEEPFILTERS function in your reports and dashboards to analyze and visualize your data accurately.