How to Use Advanced Filter in Power BI

Basic filters in Power BI are great for quick selections, but what happens when you need to ask more complex questions of your data? That’s where advanced filtering comes in, giving you the power to apply granular rules and conditions. This guide will walk you through exactly how to use advanced filters on text, numbers, and dates to find the precise insights you need.

What is Power BI Advanced Filtering?

Think of basic filtering as picking items off a list. You can select "USA" from a list of countries or tick a box for a specific product category. It’s simple and effective for straightforward tasks.

Advanced filtering, on the other hand, lets you create specific rules for what data to show or hide. Instead of just picking "USA," you could create a rule that shows all countries that start with the letter "U" or do not contain the letter "A". It lets you add compound logic, using statements with AND or OR to combine multiple rules for truly specific analysis.

When Should You Use Advanced Filters?

You’ll find yourself reaching for advanced filters when a simple checklist won’t work. Here are some common real-world scenarios:

• Analyzing Text Data: Identifying all customer feedback entries that contain the word "bug" or "error."
• Focusing on Numerical Ranges: Viewing sales transactions that are greater than $100 but less than $500.
• Setting Custom Date Ranges: Analyzing all website sessions that occurred on or after your latest marketing campaign launch and before the end of the month.
• Excluding Null or Blank Data: Cleaning up a visual by filtering where a certain field, like "Customer Email," is not blank.
• Combining Conditions: Finding all projects that are assigned to the "Marketing" department OR any project whose title contains the word "Campaign."

How to Apply an Advanced Filter: A Step-by-Step Guide

The process for applying advanced filters is largely the same whether you're working with text, numbers, or dates, but the rule options differ for each data type. Let's walk through an example for each.

First, make sure you can see the Filters pane. If it’s not visible, go to the View tab in the ribbon at the top and check the box for "Filters."

Applying Advanced Filters to Text Data

Let’s imagine we have a sales report with a table of products, and we want to find all products that are some variation of "Headphones" without manually selecting every single one.

Step 1: Select Your Visual and Field

First, click on the visual you want to filter (e.g., your product sales table). In the Filters pane, you’ll see filters are broken down into "Filters on this visual," "Filters on this page," and "Filters on all pages." Find the field you want to filter - in this case, "Product Name" - under "Filters on this visual."

Step 2: Change the Filter Type

Click the arrow to expand the card for the "Product Name" field. You'll see a small dropdown that likely says "Basic filtering." Click it and change the selection to Advanced filtering.

Step 3: Define Your Rules

The card will now change to show a rule-building interface. You will see several dropdowns and a text box.

• Show items when the value: This first dropdown contains your conditions. For text, your options include:

Let's set our first rule. To find all "Headphones," select the condition "Contains" and type "Headphone" into the text box. Note that you don't have to type "Headphones" (plural), as "Headphone" is contained in both singular and plural forms.

Step 4: Use AND/OR to Add More Rules (Optional)

Now, let’s say we want to refine this. We only want to see headphones that are also "wireless." Below your first rule, you'll see a button for And / Or. "And" means both conditions must be true, while "Or" means either condition can be true.

Let's stick with "And". We'll add another rule: Contains "wireless".

Step 5: Apply the Filter

Once your rules are set, click the "Apply filter" button at the bottom of the card. Your visual will instantly update to show only the products that match your specific criteria - namely, products that contain both "Headphone" and "wireless" in their name.

Applying Advanced Filters to Numerical Data

Now let's switch gears. Imagine we have a sales report and want to isolate sales that are mid-range deals - specifically, those between $2,000 and $5,000.

Step 1: Select the Numeric Field

Click on your chart and find the numeric field you want to filter by, such as "Order Amount," in the Filters pane.

Step 2: Switch to Advanced Filtering

Just like before, expand the field's card and change the filter type from "Basic filtering" to "Advanced filtering."

Step 3: Build Your Conditions

The condition options here are specific to numbers:

• Is / Is not
• Is greater than / Is less than
• Is greater than or equal to / Is less than or equal to
• Is blank / Is not blank

To find sales between $2,000 and $5,000, we'll create two rules:

1. Set the first rule to "is greater than or equal to" and enter 2000.
2. Keep the logic set to "And".
3. Set the second rule to "is less than or equal to" and enter 5000.

Step 4: Apply the Filter

Click "Apply filter." Your visual will now only display data where the order amount falls neatly within your defined $2,000 to $5,000 range, filtering out both small and very large deals.

Applying Advanced Filters to Date Data

Finally, let's look at date filtering, which is essential for cohort analysis, campaign tracking, and more. Suppose we ran a promotion during the second half of October 2023 and want to analyze the sales from that specific period.

Step 1: Select the Date Field

Choose your visual, then find and expand the "Order Date" field in the Filters pane.

Step 2: Choose Advanced Filtering

By default, date fields often show "Relative date filtering" (e.g., "last 7 days"). Click this dropdown and switch to "Advanced filtering."

Step 3: Define the Date Rules

Date-based conditions are very intuitive:

• Is / Is not
• Is on or after / Is after
• Is on or before / Is before
• Is blank / Is not blank

To isolate sales from October 15th to October 31st, 2023, follow these steps:

1. Set the first rule to "is on or after" and use the calendar picker to select October 15, 2023.
2. Ensure the logic is set to "And".
3. Set the second rule to "is on or before" and select October 31, 2023.

Step 4: Click "Apply Filter"

Once applied, your visual will dynamically adjust to show only the data points from within that specific promotional window.

Filter an Entire Page or Report

So far, we’ve focused on "Filters on this visual." But what if you want to apply the same filter across multiple visuals? Power BI makes this simple by letting you change the filter's scope.

Instead of dragging your field into the "Filters on this visual" box, you can drag it into either:

• Filters on this page: Applies the filter to every single visual on the current page of your report. This is great for an overview page dedicated to a specific product category or region.
• Filters on all pages: Applies the filter globally across every page in your entire report. This is useful for filtering out test data or focusing the entire report on a specific year.

The process for setting up the advanced filter rules is identical in all three scopes. You just decide at which level you want the filter to operate.

Final Thoughts

Moving from basic to advanced filtering is a game-changer for Power BI users. It elevates your reports from showing general overviews to answering highly specific and strategic business questions. By mastering the use of conditions and AND/OR logic, you can easily filter text, numbers, and dates to uncover the exact insights you’re looking for.

We built Graphed because we believe asking complex questions about your data shouldn’t require navigating complex interfaces or learning a new tool. Instead of clicking and configuring filter panes, you can just ask a question in plain English like, "show me sales for wireless headphones between $50 and $200 last quarter." We connect directly to your data sources and use AI to build dashboards and answer questions instantly, giving you the power of data analysis without the steep learning curve.