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.
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 selected as the Filter type
Use the drop-down to change it to Advanced filtering
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.
The following are the list of options available under the Show items when the value section:
- 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.
- 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.
- Starts With: If the record starts with the match value, then the record displayed.
- Does not start with: If the record does not start with the match value, then the record displayed.
- is: If the record matches exactly with the match value, then the record displayed.
- is not: If the record is not exactly equal to the match value, then the record displayed.
- is blank: This option displays all the records with the blank values.
- is not blank: It displays all the records that contain no blank values.
For the Power BI Advanced Filters demonstration purpose, we selected the Contains as the wildcard, and United as the keyword.
From the screenshot below, you can observe that the report is displaying the countries that contain the united keyword.
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.
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).
Let me use the OR operator and Starts with as the Keyword.
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.
The below screenshot is showing the Occupation that contains a manual keyword, and the Occupation that starts with P.
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.
The below report is showing the Countries that contains United keyword, and the country that is not France.
Use that tiny clear filter button to remove the Power BI Advanced Filters.
