Text Filtering in Power BI

There are lots of different ways you can filter data 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.

Text Filter in Power BI

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).

 

 

 

Share?

Comments

  1. I think an overlooked search capability is in the default slicer. I always turn this on as the default setting when there are many values in the dropdown. This can be done by hitting the ellipses and selecting search … & it works with the dropdown and list.

    Also I like the Smart Filter when there are a relatively small number of values, but have experienced long page load times when there are many of values (thousands). However, as much as I like custom visuals I try and avoid them unless they’re necessary to avoid slowing the page load times especially when viewing on mobile phones.

  2. I really like the design and features of the Smart Filter. However, I have found that, when I used it as a slicer on multiple report pages, it was incredibly resource intensive and caused crashes. Having replaced the Smart Filter with a “plain vanilla” slicer, the issues disappeared. I am not sure if it is just me or whether others have experienced this. It is a shame because I really, really like it

  3. Although I am fan of PowerBI and we have choosen it to be our solution for the next years, I also worked for Years with Qlik. The search all posibilities from Qlik is realy missing. There you could search in one selection on everything in the model, as it is in memory was not even that demanding on cpu. PowerBI is catching up fast, but still has a very long way to go !

    • Hi Harry. I am currently in Redmond with Microsoft. The thing they keep demonstrating is that they are building the features most requested by the community. Have you checked to see if there is an idea at ideas.powerbi.com? If not, you should create one and promote it for votes

      • Hi Matt, you being in Redmond is it possible that you could suggest an DMV that explains the order of the columns that dictates the compression of a DAX table and the number of run length encodings of each column. This would really help in explaining which path one should choose in a DAX expression.

  4. Great post—clearly explained how yo use these two optional custom visuals. I am currently working my way through your Learn to Write DAX so will try it as part if my exercises.

  5. Perfect timing! These work excellently with the Great Function Project P3 is kicking off!

  6. Hi Matt,

    Have you noticed any performance gain in using inbuilt Visuals than using Custom Visuals accomplishing similar tasks?

    • I have noticed the opposite actually. I have seen some custom visuals that are quite slow compared to inbuilt visuals. You just have to test well on your data set and be cautious on what you use.

  7. Matt, what about the Filter by List custom visual? Do you put that custom visual in the same category as the Smart Filter and the Text Filter?
    Also, regarding your reply to @Bibin, do you make much use of custom visuals or do you generally stick to the inbuilt visuals? Are there any custom visuals that you highly recommend?

    Ken

  8. Thanks for this Matt. As the ecosystem of custom visuals grows it is good to have some third-party info about some of them and how they might fit in with my projects.

  9. Very good article and I was able to learn some new things that I did not know before i.e. the search function under the general filter.

    Question though – to you (or anyone’s knowledge) is there a way to perform a copy and paste of a list of items and have the visual filter or any matches? I have looked and looked but have not found any solutions. TIA

    • I’m not aware of a visual that will allow you to do this. But you could work around with a hack. Create a lookup table with a single column using “Enter Data”. Link this to your data model on the column you want to filter. You can edit this Enter Data Query and cut and paste a list of values into the table, and apply a filter from there. Could that work?

  10. my frustration with the text filter is that you can’t change the font size of input box or change the height of the box.

  11. In the text filter, the delete icon does not allow values ​​to be returned to the original data.
    Has it happened to you and have you been able to solve this problem?
    I will thank anyone who can help me with this.

Leave a Reply

Your email address will not be published. Required fields are marked *

Ten Things I Wish I Knew When I Started With Power Pivot.   Learn  Without The Pain!

x