Power BI Advanced Filters

How to create Power BI advanced filters with an example?. 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, or product contains Bikes, etc.

How to use Power BI Advanced Filters

To demonstrate the Advanced Filters in Power BI, we are going to use the Clustered Column Chart that 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 Legend field, and Sales amount as the Value field.

Power BI Advanced Filters 1

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

Power BI Advanced Filters 2

Let me expand the English Country Region Name to see the filter types. By default, the Basic Filters option selected as the Filter type

Power BI Advanced Filters 3

Use the drop-down to change it to Advanced filtering

Power BI Advanced Filters 4

The following are 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 are the list of options available under the Show items when the value section:

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

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

Power BI Advanced Filters 7

From the screenshot below, you can observe that the report is displaying the countries that contain the united keyword.

Power BI Advanced Filters 8

This time we used the does not contain as the wildcard, and Canada as the keyword. From the screenshot below, you can that the report is displaying 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. From the screenshot below, you can that the report is displaying the occupations that start with M (Management and Manual).

Power BI Advanced Filters 10

Let me use the OR operator and Starts with as the Keyword.

Power BI Advanced Filters 11

Here, we are using two wildcards: the first wildcard checks whether the Occupation contains a manual keyword or not. Or, the Second filter checks whether the occupation starts with P or not.

Power BI Advanced Filters 12

The below screenshot is showing 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 Country column. First, it checks whether the Country contains a united keyword or not. Or, the Second filter checks whether the country is not France.

Power BI Advanced Filters 14

The below report is showing the Countries that contains United keyword, and the country that is 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

Comments are closed.