KEEPFILTERS

KEEPFILTERS

How to Use the Power BI DAX function KEEPFILTERS

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.

Power BI DAX Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

Subject

Your Name (required)

Company/Organisation

Email (required)

Telephone

Training Course(s)

Your Message

Upload Example Document(s) (Zip multiple files)

Copyright, G Com Solutions Ltd, 2024.
Tower Bridge Business Centre, 46-48 East Smithfield, London E1W 1AW
0800 998 9248   |   9:00 a.m. till 5:30 p.m.

Connect With Me: