Text Filtering in Power BI ✔️ - Excelerator BI Beginners

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

Steps for advance filtering in power bi

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.

Text filtering in power bi step guide

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.

Power BI text filtering screenshot

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.

Power BI Public Training Banner

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

 

Supercharge Power BI online training

57 thoughts on “Text Filtering in Power BI”

  1. Hi Matt, excellent information, thanks a lot!

    I am unable to increase the font size of the search field. The search letters are too small, and unable to increase it. Could you please help me.

    Nataraj

    1. Jason Cockington

      Great Question.
      Unfortunately the Text Filter custom visual does not have any Visual formatting options, so the text size in the search field is fixed.
      Even if you set a large font as default on the report theme, this has no impact on the Search box of the Text Filter.
      Your best solution then is to use the Slicer visual with the Search feature turned on.
      The slicer takes it’s text settings from the theme’s Text > General settings, but you can also adjust this manually on the Visual formatting options under Values.
      The downside of this approach is you’ll then need to select the options in the slicer.

      I just created an idea for allowing Visual level formatting

  2. Hey Matt, this was a helpful article. Curious, given the date written, any knowledge of additional “smart” filter/slicer visuals?

    I’ve been using the SmartFilter Pro viz recently to check out the paste capability given known use cases from our users. I’ve been looking to see if there are any additional options worth comparing against so thought I’d ask if you knew of any.

    Thanks, Bob K

  3. Hi Matt,

    Is there any way to ‘catch’ the search string you type in any of these filter visuals and dynamically display it in the title of data visuals? for instance if I used the word ‘mountain’ in my search I would like to create a title “Results of query using word ‘mountain'”

    I have tried using the standard method of creating a measure with SELECTEDVALUE() looking at the field used by the search visual but this is is always returning the fallback value of the function.

    Thanks, great article btw!

  4. Hi Matt,

    Thank you for the info about filters and Power bi. I’m trying a very basic thing but I don’t find any answer when googling the problem. I have a dashboard with Two pages/tabs and text filters in both of them, I need to filter information from 1400 buildings, I directly write the building code in the text filter and I get the info about that building. But only in one page, for the other one I need to write the building code again. Is there a way to make a text filter apply all across the pages of your report?

    Thanks!

  5. I found the SmartFilter by OKViz to be too memory intensive, it would lock the whole report for minutes. Great article thank you.

  6. Hi Matt, what a remarkably well composed article! Thanks for taking the time to make this and help us out!

    Do you know of any way I can have a slicer/text field that I can use to EXCLUDE data from results? For example, if I have 450 results, but 75 of those results contain the word “widget” in the description field, I want to be able to manually type “widget” into a field (or better yet, type in multiple words or phrases in quotes) and have the results auto refresh to exclude those 75 records…

    Does that make sense?? 🙂

  7. Hello Matt,

    I am trying to force users to type at least three characters in Microsoft Text Filter search before filtering the results. Do you know if that is possible?

    Thank you

      1. Okay, maybe force was the wrong way to say it. How about if when the user types in A, the visual being filtered shows nothing because input length is less than 3 or something like that?

        Totally understand if that is also not possible.

        1. There is no VBA object model or config settings to control how many characters must be entered before searching. You could simply add a text box saying “type at least 3 characters”, or a tool tip maybe

          1. Thanks again for your reply. I have one last question if you would be so kind!

            Is there anyway to use what the user has typed in? I have tried SELECTEDVALUES but that isn’t working because I have a large dataset so a word like Agency might return multiple rows and I would like to display what the user searched on a different page.

  8. Capacity!
    I’ve just tried the slicer with a database of 1.7 million place names. It gives a wee message saying there are too many variants. Same with Ok viz tool.

  9. Hi Matt,

    Great post. Would you know if it is possible to extract and store the typed value of a text filter into a parameter that can be used for evaluation purposes, using a DAX measure ?

    The classic way forward, using a single select filter exists for this purpose, but I wonder if it would also exist with the MS text filter.

    Immanuel

      1. Hi Matt,

        Thanks for the reply. In the Text Filter by Microsoft visual, the user has to type in a given ‘search value’, which onward will trigger the search. Question is whether or not it is possible to retrieve or store this value in some sort of parameter.

        In the simplest form I would expect to display the search value/parameter in a simple card visual.

        1. I don’t know of any way to make a search term “persistent” in Power BI. Once you search for something else, the previous values are lost. It is possible that you could use “edit data” in Power BI desktop to add values to a table, and then somehow write a formula to use those values. But I don’t think it would be easy and I don’t think it would be user friendly. I think the bigger question is “why do you need to search, and reuse those search terms”?. Maybe there is a better way to solve the problem, what ever that is.

  10. In my Power BI report I noticed that Text Filter custom visual by Microsoft is significantly slower compared to the native slicer visual. Any idea why?

    1. I would expect anytime you do a text based string search, it will be slower than a hard coded list of values to pick from. The model stores the lists (columns) efficiently. If you are doing a text search, it has to interpret what you want and then check every item in the list.

  11. Hi Matt –

    Is there anyway we can search for the text in the slicer and that exact whole text/word search if I hit the search button?

    Currently it is a wild card search.

  12. Hi Matt,
    I am looking for a search functionality which will search everything in the report, not just a column. Is that possible?

  13. Hi Rodney. I spoke to Marco Russo after I saw your comments. He said that Smart Filter Pro has many more features and has already solved most of the issues you mentioned. In addition, there is a new version of Smart Filter Free which also has solved many bugs and performance issues. This version is with Microsoft for certification. As soon as Microsoft approves it, there will be an update available.

  14. CARLOS GUSUKUMA

    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.

    1. Hello,
      There is a problem that I have noticed is that after using the filter text and emptying it, the results do not return to the initial state. it remains with the same values during the search and so each time you have to refresh the page.
      Thanks for your help.

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

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

    1. 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?

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

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

  19. Hi Matt,

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

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

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

  21. 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 !

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

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

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

    1. Yes Harry, I had performance problems using Smart Slicer, it couldn’t actually display the list of titles (said the list was too large) and really slowed down the return of results when compared to the standard slicer. Shame, hope they improve it.

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

Leave a Comment

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

Scroll to Top