Text Filtering in Power BI

There are lots of different ways that you use to do Text Filtering in Power BI including:

• Slicers (with various configurations)
• Cross filtering from any visual object
• The filters pane on the right hand side of your report
• Custom visuals designed specifically for filtering

Most of these standard filters require you to select on a specific value from a list (List of Values). There are exceptions, notably the filters pane on the right hand side has an “Advanced Filter” where you can search within a field/column values (shown as 1 and 2 below).  In addition you can do some complex AND/OR logic (3 below).

There is nothing wrong with this approach however on some occasions you may want to filter visuals in your Power BI Report page by text from the report canvas itself.  In this article I will show you how to filter the Adventure Works database looking for product model names using text strings.

Power BI Custom Visuals for Text Filtering

Power BI has two (at least 2) custom visuals that facilitate filtering the visuals on a Report page using text:

• Text Filter by Microsoft
• Smart Filter by OkViz

I will explain how to use these text filters and then discuss their advantages and drawbacks. You can choose to use either of them based on your requirement. Both of these text filter visuals need a column to use as the field that is of text type to use for filtering. Then the search is performed on the values of that field and only the matching values will be displayed in all the visuals on the report page. For illustration, I am using Products[ModelName] column in the following examples.

Find and Import Custom Visuals

Unlike the standard visuals in Power BI, you always need to find and import custom visuals before you can use them.

You can find the custom visuals in Microsoft Apps gallery.

To download any custom visual, click on the ellipses (see #1 below) in the VISUALIZATIONS pane and then select

1. Import from file (#2 below), or
2. Import from marketplace (#3 below)

If you choose the option Import from file, you need to first download the custom visual and then select that option. If you choose the option Import from marketplace, you can directly add it to Power BI Desktop.

After importing these custom visuals, you can find their icons – Text Filter (#1 below) and Smart Filter (#2 below).

Text Filter by Microsoft in Action

In the following report, I have 4 visuals:

1. A Text Filter (#1 below) with Products[ModelName] on Field.
2. A Matrix (#2 below) with Products[Category] and Products[ModelName] on Rows and [Total Orders] and [Total Sales] on Values.
3. A Clustered column chart (#3 below) with Products[ModelName] on Axis and [Total Orders] on Value.
4. A Treemap chart (#4 below) with Products[ModelName] on Group and [Total Sales] on Values.

I typed mountain in the Text Filter (#5 below) and pressed Enter.

You can see that all the 3 visuals are filtered to display only those values wherein the ModelName contains the word mountain.

As you can see below, it is possible to type any text into the search box, press enter and see the filter applied to the report.

Tips for Using the Text Filter

Displaying the Title in the Text Filter

You can choose to display the Category (the Text field) you have used as the Title for the Text Filter so that the user will know what text can be typed in the search box.  I turned on Title (#1 below) and then typed ModelName in the Title Text box (#2 below) to indicate that the ModelName field is used for the text search and filtering.

Clearing the Filtering done by the Text Filter

You can click on the eraser icon (#1 below) to clear the text in the Text Filter.

Note: both the Text Filter and Smart Filter have an eraser widget, with which you can clear the search text and along with it the filtering applied to the visuals by that text.

Advantages of the Text Filter

• The text you used to search and filter the visuals (see #5 above) remains till you erase it. So at a glance, you will know what the visuals are filtered for, with the Text Filter.
• The Text Filter is case insensitive. So if you search for mountain, it will filter the visuals for both mountain and Mountain.
• All items in the list that contain the search term will be retained in the filter.
• You can use just a few characters to search for the text.
• The size of the Text Filter visual can be put as small as any other search box.

Drawbacks of the Text Filter

As of the date I am writing this article, you cannot use multiple key words for search in the Text Filter. i.e. you cannot search for patterns like Road AND Mountain, or Road OR Mountain.  This can be done in the Filters pane as described earlier.

Smart Filter by OKViz Features

The OKViz Smart Filter can be used in 3 ways:

1. As a Text Search box just like the Text Filter that you have seen above, but with more flexibility.
2. As a Slicer with dropdown list to choose the values for filtering.
3. As an Observer displaying the current filters set in the Report page with the specific field.

Smart Filter as Text Search box

In the following report, I have 4 visuals:

1. A Smart Filter (#1 below) with Products[ModelName] on Field.
2. A Matrix (#2 below) with Products[Category] and Products[ModelName] on Rows and [Total Orders] and [Total Sales] on Values.
3. A Clustered column chart (#3 below) with Products[ModelName] on Axis and [Total Orders] on Value.
4. A Treemap chart (#4 below) with Products[ModelName] on Group and [Total Sales] on Values.

I typed mountain in the Smart Filter (#5 below).

As I typed the characters, all the matching values of the field appear in the dropdown list (#6 below).

If I press Enter as I did for the Text Filter, Smart Filter picks the item highlighted in yellow (see Smart Filter image above). If you want to select a different value, you can use the up/down arrows on the keyboard, or simply click on a different value in the dropdown list.

Selecting Multiple Values

Suppose, you want to select more than one matching value. You need to make changes to the visual configuration as follows:

• In the Format pane, under Filter Box, set Max selectable items (see #1 below) to a number you want. I typed in 3. By default, it is set to Auto and you can select only one value.

As shown below, I was then able to select 3 matching values (each value was a separate text search).

The Report page then looked as shown below.

Compress Multiple Items

In the Filter configuration pane (shown earlier), you can find two more options (marked as #2 and #3 in the earlier image).  If you set Compress multiple items (#2 above) to on, the Report page looks as follows.  As you can see, the downside of this is that you can’t actually see what filters have been applied.  But this is consistent with the standard drop down filter in Power BI.

Smart Filter as a Slicer

If you click on the down arrow on the right-side of the text box, a list of values is displayed similar to the standard Power BI slicer drop down list.

Smart Filter as an Observer

The third setting (#3 above) allows you to use a Smart Filter as an Observer. Basically this means that the Smart Filter will “watch for filters” applied in other places in your report, and display a list of filters that have been applied.  In this mode, it doesn’t act as a slicer at all.  To use this, you must first turn this feature on in the settings.  Then if you click on values in other visuals, Smart Filter as an Observer displays the values that have been filtered.  You could change the title of the Smart Filter in this case to read “Filtered Items in this Report”.

Advantages of Smart Filter

• You can use just a few characters to search for the text.
• Smart Filter is case insensitive. So if you search for mountain, it will filter the visuals for both mountain and Mountain.
• You can provide the Category (field) by which you are filtering as the Title so that it would be self-explanatory.

Drawbacks of Smart Filter

• If you set to select more than one value, you need to type the search text repeatedly for all the values.
• The size of the Smart Filter visual cannot be put as small as any other search box. You need to provide space for the drop down list.
• You cannot use multiple key words for search in the Text Filter. i.e. you cannot search for patterns like Road AND Mountain, or Road OR Mountain. This can be done in the Filters pane as described earlier.

Default Slicer Search

Update 7 March.  Tony made a comment below directing me to the search feature in the default slicer.  I had never seen that before, but indeed it is great.  Here is how you can turn it on and how it works.

• Insert a Slicer on the Report canvas.
• Select Products[ModelName] for Field.
• Click on the ellipses (see #1 below).
• Select Search (see #2 below).

• Type mountain on the Search line (see #1 below).

Note that only those options containing mountain remain in the slicer (see #2 below).

Also note that the Select All option also disappears.

As shown below, I selected 3 matching values and pressed Enter.

The visuals on the Report page got filtered to those values.

You can clear the filters as you normally do with the Slicer – clicking on Clear selections icon (see #1 below).