Power BI Filters

Power BI Filters are useful to restrict the data as per the requirements. For example, we can use basic filters to exclude a product from the display or display top/bottom 10 performing records. Let me show you how to create Power BI basic filters with an example.

To demonstrate the Basic Filters in Power BI, we will use the Clustered Column Chart we created in our earlier post. As you can see from the screenshot of Power BI below, we used the Country Name as the Axis field, Occupation as the Legend field, and Sales amount as the Value field.

How to use Power BI Basic Filters?

Under the Filters section, you can see the list of filters that are available in Power BI.

Add Fields to Visual Level Filters 2

Let me expand the English Country Region Name (in Short Country) to see the filter types.

Visual Level Filters Type 3

Power BI Filter Types

Use the drop-down list to see the filter types available for dimension.

  • Advanced Filtering: This option helps you to use more advanced options like starts with, ends with, contains, etc.
  • Basic Filters: It is used to perform basic operations.
  • Top N: Use this to find the top 10 records or bottom 10 records.

We want to discuss the basic filters in this example, so let me select the same. Selecting Basic Filters in Power BI displays the available rows for this column. You can use the checkboxes to select One, None (or All)

Let me select Australia, Germany, the United Kingdom, and the United States. The screenshot below shows that the clustered column chart displays the column that belongs to sleeted countries.

Power BI Basic Filters 5

Required Single Selection: It restricts us from selecting one field at a time. Now you can choose only one country name at a time. As you can see, we selected the United States.

Power BI Basic Filters 7

By unchecking the Power BI filter Required Single Selection property, you can select multiple fields.

Multiple Fields for Basic Filtering 8

Clicking that little tiny clear filter button helps you remove the existing filters.

Clear Button 9

Power BI basic Filters on Multiple dimensions

Let me expand the English Occupation column to apply filters on Occupation. Please select the Management, Professional, and Skilled Manual fields as the basic filtering values. As you can see from the screenshot below, it displays 4 countries that we selected in Countries Filters and Three occupations that we chose now.

Power BI Basic Filters 11

This time, we select a single field (Require Single Selection option), i.e., Skilled Manual.

Choose Value to see the changes in a chart 12

Delete Power BI Filters

Click on the tiny little Clear filter button to delete existing filters. For the demo purpose, let me delete the filter on the Occupation field.

Click the Clear button 13

You can see that the Occupation field has no Filters

Power BI Basic Filters 14

Let me remove the filters on the Country

Clear one kore field 15

Now you can see there are no filters in this Clustered Column Chart.

Power BI Basic Filters 16

How to use Power BI Advanced Filters?

Power BI Advanced Filters use Wildcards to restrict the report data as per the requirements. For example, we can use these Power BI advanced filters to find products that start with S, products containing Bikes, etc.

To demonstrate the Advanced Filters in Power BI, we will use the Clustered Column Chart we created in our earlier post. As you can see from the Power BI screenshot below, we used the Country Name as the Axis field, English Occupation as the Legend field, and Sales amount as the Value field.

Under the Filters section, you can see the list of available filters in Power BI. Let me expand the English Country Region Name to see the filter types. By default, the Basic Filters option is selected as the Filter type

Power BI Advanced Filters 3

Use the drop-down under the Filter Type to change it to Advanced filtering. The following is the list of options that are available for Power BI Advanced filtering.

  • Show Items when the Value: Please select the wildcard that you want to use.
  • Empty Text box: Please specify the wildcard character or expression. For example, it starts with s.
  • And and Or: Same as AND and OR Operators in SQL. Use this to concatenate multiple wildcard searches.
  • Empty drop down list: Please select the second wildcard that you want to use.
  • Empty Text box: Please specify the character or expression for the second wildcard.
Power BI Advanced Filters 5

The following is the list of Power BI Filters options available under the Show items when the value section:

  1. Contains: When selecting this property, it checks each record against the Match Value. If it contains the match value at any position, the record will display.
  2. Does not Contain: It checks each record against the Match Value. If it does not contain the match value at any position, the record will display.
  3. Starts With: If the record starts with the match value, then the record will display.
  4. Does not start with: If the record does not start with the match value, then the record will display.
  5. is: If the record matches exactly with the match value, then the record will display.
  6. is not: If the record is not exactly equal to the match value, then the record will display.
  7. is blank: This option displays all the records with blank values.
  8. is not blank: It displays all the records with no blank values.

Wildcard Options

For the Power BI Advanced Filters demonstration purpose, we selected Contains as the wildcard and United as the keyword, and clicked the Apply Filter link.

The screenshot below shows that the report displays the countries that contain the united keyword.

Power BI Advanced Filters 8

This time we used the does not contain as the wildcard option and Canada as the keyword. From the screenshot below, you can see that the report displays all the countries except Canada.

Power BI Advanced Filters 9

Let me apply Power BI Advanced Filters on the English Occupation column.

We used the starts with as the wildcard and M as the keyword. The screenshot below shows that the report displays the occupations that start with M (Management and Manual).

Power BI Advanced Filters 10

Let me use the OR operator, and Starts with will be the Keyword. Here, we are using two wildcards as Power BI filters: the first wildcard checks whether the Occupation contains a manual keyword or not. The Second filter checks whether the occupation starts with P or not.

The below screenshot shows the Occupation that contains a manual keyword and the Occupation that starts with P.

Power BI Advanced Filters 13

This time we are applying multiple filters on the Country column. First, it checks whether the Country contains a united keyword or not. The Second filter checks whether the country is not France.

The below report shows the Countries that contain the United keyword and those that are not France.

Power BI Advanced Filters 15

Use that tiny clear filter button to remove the Power BI Advanced Filters.

Power BI Advanced Filters 16