Select a product:
Introduction to Autocomplete
This video illustrates some important concepts and settings related to the Autocomplete visual. It uses a real-world use case, namely address searching, and how "contains" versus "exact" matching can lead to very different outcomes. (It also shows synergy between the Autocomplete visual and the Timelines visual.)
A one minute introduction to the Autocomplete visual.
This video illustrates the use of three specific settings that control matching behavior. It's the first in a series of "how to" videos (follow our YouTube channel for the latest).
This 4 minute video illustrates synergies between the Autocomplete visual and the Text from Values visual.
Use Cases
Our sample PBIX file illustrates a number of real-world use cases where Autocomplete makes a difference. If you'd like to see more concrete examples, send us an email and there's a good chance we'll add your scenario to our sample!
Useful Settings
Definitions
This diagram labels (in red) different data entry mechanisms that the visual supports:
Note: not all settings are covered in this document, but we've selected ones where accompanying pictures may help to further illustrate the setting.
Select Single or Multiple? Filter Styles.
This fundamental setting controls whether the visual allows entry of one filter term, or multiple.
Single selection only needs to show a single text box, where multiple entry offers two modes of entry: the search text box (aka Entry Text Box) and the accumulated filter term area (aka Filter List Area). The accumulated filter area can either be a text area, or a list of values which resemble rounded "pills".
In version 3.0, a new entry style was added: "Text Pop-up". This hides the filter list area until explicitly invoked. The button used to invoke it will show a red circle glyph when there is at least one filter term in place.
It's possible to let the user toggle the entry style between Text and Pills:
Filtering Behavior and Entry
At the most fundamental level, you can control whether you're filtering to match values or exclude values.
You can also control whether a) the visual only starts to filter when all data for it is fully loaded, and b) whether finding no matches leads to cross filtering out all data or simply ignores the fact there are no matches.
As you type a value in the entry text box, autocompletion may try to append the "top match" to what you're actively typing. You can disable this behavior with the 'Attempt Completion' setting.
With the 'Word Splitting' setting, if you type multiple words in the entry text box, either the whole phrase (as typed) must match (Disabled), all words must match (Intersect), or any word must match (Union). (The example below shows Union enabled.)
The 'Casing' setting can force the case of text entered in the entry text box to upper or lower case, if desired. 'Auto-detect' (the default) examines the source data to determine if it is all upper or lower case to determine what to enforce.
When 'Can Change Case' is enabled (the default), text typed in the entry text box can have its case changed, to match source values. For example below we typed 'oscar c' and the case has been changed to match the source's value of 'Oscar Carson'.
Buttons / Links
Most buttons can be switched out to use links, instead. (The default is to use buttons.)
Text that's shown for button tool-tips or link text can be changed using "message" setting, under Formatting. (Note that virtually all text messages can be overridden in settings.)
Button images can be overridden with your own images of choice. You'll find one setting per button. This setting would be used for the Reset button:
Buttons / links for different features can be selectively shown, using toggle settings. For example, the "Add All Loaded" button is invisible by default, unless it is explicitly enabled here:
Buttons / links available:
- Add Current: Adds value currently in the entry text box to the filter area. How the add is performed is controlled by settings such as 'Retain Partial Match on Add' and 'Expand on Add'.
- Reset: (aka 'Clear''.) Clears any current filters. (Both in the entry text box and filter list.)
- Can Toggle Show Blanks: When enabled, shows a toggle button that switches between including or excluding blank values in the filter.
- Can Toggle List Style: When enabled, shows a toggle button that switches between presenting the filter list as an editable text area or a set of read-only text values.
- Can Toggle Include Exclude: When enabled, shows a toggle button that switches from including the filter items to excluding them.
- Add All: When enabled and there are source values loaded into the visual, shows a button that will take all distinct source values and place them in the filter list. This can be used to identify source values that are 'currently filtered' based on report cross filters.
- Get List: When enabled, a button is shown that, when clicked, brings up the pick list. (As opposed to requiring typing of new values in the entry text box to bring up the pick list.)
- Copy Clipboard: This button, when clicked, copies all values in the filter list to the clipboard.
Using Multiple Filter Terms
Filter terms can be combined using logical "AND" or "OR". (Default is "OR" = Union.) Union means any filter list item match allows the source row to be "seen", where Intersect means all filter list items must match in each source row.
Starting in version 3.2, you can have finer-grained control over filter term combining. Enabling the 'Allow Filter Prefixes' setting lets you prefix filter list items with '+' (OR), '&' (AND), or '-' (NOT). Consider this example:
Here we have enabled "Allow Filter Prefixes", and added "&le" and "&nn" as filter terms. Notice that the cross-filter to the table on the right shows only names that contain both "le" and "nn" in their body. Now let's change the set of filter terms to include "-qu":
Note how the table on the right has changed to no longer include items like "Quinn Cole", which contains "qu" as text. If we instead use "+" as a prefix, this example shows names that contain either "kim" or "chen" but not "bob":
When multiple filter items are allowed, you can control whether what you're actively typing in the entry text box will "filter out" to other visuals on the page, or whether filtering only happens for "committed values" (i.e. are in the filter list text area / pill list).
When 'Selecting Auto-Adds' is enabled (the default), picking an item in the pick list will add it to the filter list. (It will also close the pick list if 'Keep List Open' is false.) If it's disabled, then the picked value will only be placed in the entry text box, offering the user the option to add it to the filter list using Enter or clicking the Add button/link.
When 'Retain Partial Match on Add' is enabled, the pick list can remain open, even when an item is picked. In this case, the item is added to the filter list, and removed from the pick list. This makes it easy to select other values to add which share the same partial match text. In the example below, 'osca' was typed, 'Oscar Chen' was picked, and then 'Oscar Duarte' was picked. The pick list remained open until focus was lost on the entry text box. Notice that the two picked values were added to the filter list.
'Keep List Open' is similar, where the list will remain open after a selection is made, and focus must be lost or a click on the Close pick list link. In this example, we're picking two names in sequence.
When 'Enter Adds Single' is enabled, if the visual has suggested additional, non-typed text and you press 'Enter', the full match will be added to the filter list. Otherwise, only the typed portion will be added. Notice how partial matches in the filter list area, by default, filter all rows that relate to the filter text (using all other settings that control matching behavior).
'Expand on Add', when enabled, changes the nature of the Add button / link, and potentially the Enter key if 'Enter Adds Single' is disabled. Instead of adding the item being entered, the visual identifies all actual matches in the source data and adds them to the filter area. This has the effect of letting you get a full list of matches, from a partial search term. This list can in turn be copied out of the visual, using the clipboard.
When 'Only Allow Matched' is enabled, partial matches are not used for filtering. This generally requires use of the pick list, or pasting in full values.
'Maximum to Filter' controls the maximum number of distinct values in the value source that can be filtered by the visual. The default is 10,000. Increase this at the risk of performance issues.
When 'Filter Select All' is enabled, putting focus in the filter list area (when text), the visual will automatically select all text in that text area. (Makes copying it or clearing it easier.)
Matching Algorithms
Normally, text that's entered in either the entry text box or filter list area must match value source values exactly. However, a number of settings can relax this rule. The remainder of this section discusses these settings.
When 'Case Must Match' is enabled (default is disabled), then the case of the typed text must match the casing in the source data, for it to be considered a match. In this example, the setting is enabled and nothing contains lower-case 'oscar', but does contain 'Oscar':
'Ignore Non-alphanumeric', when enabled, ignores non-alphanumeric characters in both value source data and entered values. The examples below illustrate how you could enter a value with no whitespace and it would match source values with whitespace, or you could include "extra" whitespace in the entered value and it would match source values without whitespace.
'Keep Whitespace' provides an exception to 'Ignore Non-alphanumeric' (and all other settings), where whitespace is required to match whitespace. (Multiple consecutive whitespace characters are treated as one.)
The 'Retain Characters' setting lets you override 'Ignore Non-Alphanumeric', to include characters of your choice in matching. For example '@' might be an important character as part of an email lookup. In this case, entering '@bob' would match 'fred@bobco.com' but not 'bob@xyz.com' whereas with just 'Ignore Non-Alphanumeric' enabled, both would match.
Enabling the 'Diacritic Neutral' setting allows matching between diacritic characters and their non-diacritic equivalents. For example, 'é' and 'e' are treated the same. In the example below, we've typed a name with no diacritic characters, but was able to filter to a source value that does contain diacritic characters.
Enabling 'Fuzzy Matching' changes matching rules, after other matching rules have been applied (such as ignore non-alphanumeric, keep whitespace, etc.).
Fuzzy matching allows you to find values in the value source which are not exact matches with your entered text.
Depending on the algorithm chosen, you can locate similar matches, such as matches that are "close" phonetically. Each available algorithm is described in more in Help.
Here are two examples where Double-Metaphone is used. Notice that matches can be found which are "close" to the typed values.
The 'List Not Fuzzy' setting, when enabled, forces fuzzy matching to only apply to the pick list lookup. Actual filtering out from the visual is based on all matching rules except for fuzzy matching being enabled. This assumes you might be finding real source values using the pick list and adding them to the filter list. If this setting is left disabled, added filter items can expand what is actually filtered to include items that were not technically "picked" by the user, since fuzzy matching would be applied to each filter term. The example below shows the difference between 'List Not Fuzzy' set to false versus true. When false, an item not containing 'johnson' was matched, due to being only similar.
Secondary Values
For the purposes of this section, we'll use this field configuration:
If we type 'carlos c', notice that the pick list contains additional details - not just the name. These secondary values are not included in the matching, but relate back to the value source, per row. Notice the pick list is sorted by the sales amount measure, in descending order. Notice the
sales amount format is being used from the semantic model.
The general style of the secondary text can be controlled by the 'Secondary Style'. Below we illustrate the use of a custom delimiter.
A format string provides a way to build a secondary string that contains both literal text and secondary values (first is {0}, second is {1}, etc.).
Multiple Value Source Fields
Starting in version 5.2, you can drag up to four fields into the Value Source well (all from the same table). The visual joins each row's values into a single combined lookup string using the 'Value Separator' setting (default: " - "). For example, with an address column and a city column, a row could appear as "123 Oak Street - Springfield" in the pick list.
Everything you already know about matching applies to the combined string: autocomplete, exact and contains modes, fuzzy matching, batch expansion, and so on all treat "123 Oak Street - Springfield" as one searchable value. This means you can find a row by typing text from any of the combined fields - a city name works just as well as a street number.
When you pick a combined value, the visual filters the underlying rows across all of the provided columns - each selection identifies its exact row combination (street AND city together), and multiple selections are combined with OR. Under the hood this uses a Power BI tuple filter rather than the single-column filter used when one field is provided.
Things to Know
- All Value Source fields must come from the same table. If fields span tables, the visual shows a warning and does not filter until the configuration is corrected.
- Exclude mode is not supported with multiple value fields (Power BI tuple filters only support inclusion). The visual shows a warning if exclude mode is active; switch to include mode to apply the filter. With a single field, exclude mode continues to work as before.
- The order of fields in the well defines the order of values in the combined string. Reordering, adding, or removing fields rebuilds the visual's search index automatically.
- Choose a 'Value Separator' that doesn't naturally occur inside your data values - it keeps the combined strings readable and unambiguous in the pick list.
- The 'Filter Default' field (when used) matches against the combined string, not the individual field values.
- Removing extra fields (back to a single field) returns the visual to its standard single-column filtering behavior with no other changes needed.
- Dragging a hierarchy into Value Source no longer produces drill-down arrows (a change from 5.1 and earlier). The hierarchy's levels are treated as multiple value fields and combine into a single lookup string, subject to the same 4-field limit.
Layout Options
You can specify the maximum number of items to show in the pick list using the 'Matches Shown' setting.
Enabling the 'Show Close List Link' causes an additional item to be added to the pick list (scrolling may be required), namely a link that when clicked, causes the pick list to close. This offers a way to close the pick list explicitly, as opposed to needing to lose focus from the entry text box. The text for the link can be changed using the 'Close List Text' setting.
'List Placement' lets you change where the pick list is placed. Each possible setting is shown below.
'List Height Ratio' lets you control the maximum size of the pick list. The example below shows the default setting of '95' versus '50' in the next figure.
Other Advanced Features
'Expand Only if Contains' is similar to 'List Not Fuzzy', except it applies when expanding matches (when 'Expand on Add' is enabled): matches must contain the partial match text as typed to be expanded into the filter list. This is most useful when fuzzy matching is enabled, and you may not want to add unexpected values. In the example below, 'kim ch' has two possible matches with double-metaphone enabled, but hitting Enter (or clicking Add) will only add 'Kim Chen' since the typed text appears in the match.
'Maximum Lexical Distance' lets you fine-tune how similar pick list suggestions need to be from your typed text. In this example, when the maximum lexical distance is set to '3', we get two possible matches when typing 'kim ch'. However, changing it to '2' results in only one possible match.
'Blank Lookup Text', when provided, becomes a data value that's included in the value source. When it's 'picked' either by entering or picking from the pick list, it matches blank values which might be present in the value source. (In fact, this only has an effect if there is at least one blank value in the source.)
'List Item Matching' lets you add filter items which contain a superset of the text intended for matching. For example, you could paste a name followed by a semi-colon, followed by an address into the visual, with an intention to only match by the name.
