How to create advanced Power BI filters with an example ?. Power BI advanced filters use wildcards to narrow the report data based on requirements. For example, we can use these advanced Power BI filters to find products that start with S, or that the product contains bikes, etc.

How to use advanced filters in Power BI

To demonstrate advanced filters in Power BI, we'll use the clustered column chart that we created in our previous post. As you can see in the Power BI screenshot below, we use the country name as the Axis field, the English occupation as the Legend field, and the sales amount as the Value field.

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

Let me expand the country region name in English to see the filter types. By default, Basic Filters option selected as Filter Type

Use the drop down menu to change it to advanced filtering

The following is the list of options that are available for Power BI advanced filtering.

• Show items when value: Select the wildcard you want to use.
• Empty text box: Specify the wildcard character or expression. For example, start with s.
• And me: Same as the AND and OR operators in SQL. Use this to concatenate multiple wildcard searches.
• Empty dropdown list: Select the second wildcard you want to use.
• Empty text box: Specify the character or expression for the second wildcard.

The following is the list of options available in the Show items when 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, the record is displayed.
2. It does not have: Compare each record with the Match value. And if it doesn't contain the match value at any position, the record is displayed.
3. It starts with: If the record begins with match value, then the log is displayed.
4. It does not start with: If the registration does not start with the match value, then the log is displayed.
5. is: If the record exactly matches the match value, then the log is displayed.
6. It is not: If the record is not exactly the same as match value, then the log is displayed.
7. it is blank: This option displays all records with blank values.
8. not blank: Shows all records that do not contain blank values.

For the purpose of demonstrating the advanced Power BI filters, we selected the Contains as a wildcard and United as a keyword.

In the screenshot below, you can see that the report shows the countries that contain the joined keyword.

This time we use the it does not have as a wildcard and Canada as a keyword. In the screenshot below, you can see that the report shows all countries except Canada.

Let me apply Power BI advanced filters on the English Occupation column.

We use the starts with as a wildcard and M as a keyword. From the screenshot below, you can see that the report shows the occupations that start with M (Administration and Manual).

Let me use the OR operator and it starts with as the keyword.

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

The following screen shot shows the Occupation that contains a manual keyword and the Occupation that begins with P.

This time we are applying several filters in the Country column. First, check if the country contains a unified keyword or not. Or, the second filter checks if the country is not France.

The following report shows the countries that contain the keyword United and the country that is not France.

Use that little transparent filter button to remove advanced Power BI filters.

How to create advanced Power BI filters with an example ?. Power BI advanced filters use wildcards to narrow the report data based on requirements. For example, we can use these advanced Power BI filters to find products that start with S, or that the product contains bikes, etc.

How to use advanced filters in Power BI

To demonstrate advanced filters in Power BI, we'll use the clustered column chart that we created in our previous post. As you can see in the Power BI screenshot below, we use the country name as the Axis field, the English occupation as the Legend field, and the sales amount as the Value field.

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

Let me expand the country region name in English to see the filter types. By default, Basic Filters option selected as Filter Type

Use the drop down menu to change it to advanced filtering

The following is the list of options that are available for Power BI advanced filtering.

• Show items when value: Select the wildcard you want to use.
• Empty text box: Specify the wildcard character or expression. For example, start with s.
• And me: Same as the AND and OR operators in SQL. Use this to concatenate multiple wildcard searches.
• Empty dropdown list: Select the second wildcard you want to use.
• Empty text box: Specify the character or expression for the second wildcard.

The following is the list of options available in the Show items when 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, the record is displayed.
2. It does not have: Compare each record with the Match value. And if it doesn't contain the match value at any position, the record is displayed.
3. It starts with: If the record begins with match value, then the log is displayed.
4. It does not start with: If the registration does not start with the match value, then the log is displayed.
5. is: If the record exactly matches the match value, then the log is displayed.
6. It is not: If the record is not exactly the same as match value, then the log is displayed.
7. it is blank: This option displays all records with blank values.
8. not blank: Shows all records that do not contain blank values.

For the purpose of demonstrating the advanced Power BI filters, we selected the Contains as a wildcard and United as a keyword.

In the screenshot below, you can see that the report shows the countries that contain the joined keyword.

This time we use the it does not have as a wildcard and Canada as a keyword. In the screenshot below, you can see that the report shows all countries except Canada.

Let me apply Power BI advanced filters on the English Occupation column.

We use the starts with as a wildcard and M as a keyword. From the screenshot below, you can see that the report shows the occupations that start with M (Administration and Manual).

Let me use the OR operator and it starts with as the keyword.

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

The following screen shot shows the Occupation that contains a manual keyword and the Occupation that begins with P.

This time we are applying several filters in the Country column. First, check if the country contains a unified keyword or not. Or, the second filter checks if the country is not France.

The following report shows the countries that contain the keyword United and the country that is not France.

Use that little transparent filter button to remove advanced Power BI filters.

How to create advanced Power BI filters with an example ?. Power BI advanced filters use wildcards to narrow the report data based on requirements. For example, we can use these advanced Power BI filters to find products that start with S, or that the product contains bikes, etc.

How to use advanced filters in Power BI

To demonstrate advanced filters in Power BI, we'll use the clustered column chart that we created in our previous post. As you can see in the Power BI screenshot below, we use the country name as the Axis field, the English occupation as the Legend field, and the sales amount as the Value field.

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

Let me expand the country region name in English to see the filter types. By default, Basic Filters option selected as Filter Type

Use the drop down menu to change it to advanced filtering

The following is the list of options that are available for Power BI advanced filtering.

• Show items when value: Select the wildcard you want to use.
• Empty text box: Specify the wildcard character or expression. For example, start with s.
• And me: Same as the AND and OR operators in SQL. Use this to concatenate multiple wildcard searches.
• Empty dropdown list: Select the second wildcard you want to use.
• Empty text box: Specify the character or expression for the second wildcard.

The following is the list of options available in the Show items when 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, the record is displayed.
2. It does not have: Compare each record with the Match value. And if it doesn't contain the match value at any position, the record is displayed.
3. It starts with: If the record begins with match value, then the log is displayed.
4. It does not start with: If the registration does not start with the match value, then the log is displayed.
5. is: If the record exactly matches the match value, then the log is displayed.
6. It is not: If the record is not exactly the same as match value, then the log is displayed.
7. it is blank: This option displays all records with blank values.
8. not blank: Shows all records that do not contain blank values.

For the purpose of demonstrating the advanced Power BI filters, we selected the Contains as a wildcard and United as a keyword.

In the screenshot below, you can see that the report shows the countries that contain the joined keyword.

This time we use the it does not have as a wildcard and Canada as a keyword. In the screenshot below, you can see that the report shows all countries except Canada.

Let me apply Power BI advanced filters on the English Occupation column.

We use the starts with as a wildcard and M as a keyword. From the screenshot below, you can see that the report shows the occupations that start with M (Administration and Manual).

Let me use the OR operator and it starts with as the keyword.

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

The following screen shot shows the Occupation that contains a manual keyword and the Occupation that begins with P.

This time we are applying several filters in the Country column. First, check if the country contains a unified keyword or not. Or, the second filter checks if the country is not France.

The following report shows the countries that contain the keyword United and the country that is not France.

Use that little transparent filter button to remove advanced Power BI filters.