Forum Discussion
Search multiple tables/visuals with on textbox that a user inputs the search term
If i have 3 tables, each with multiple columns where there are no relationships. But they have either an email address or firstname or lastname fields. I would like a way for a user to enter part of a user's name in a search box and it filter all the visuals in report view.
EX:
Table 1
| Email address | Org |
| [email protected] | marketing |
| [email protected] | finance |
| [email protected] | CEO |
Table 2
| Firstname | Last name |
| John | Doe |
| Jane | Doe |
| George | Smith |
Table 3
| Name | Company |
| John Doe | ABC |
| Jane Doe | DEF |
| John Smith | HIJ |
I have a table visual for each of these tables.
I want a search box/filter where the user can type in any part of a name, and the 3 tables filter to show those rows where:
-Table 1 filtres on the "email" field
-table 2 filters on "first name" or "last name"
-Table 3 filters on 'Name' column
5 Replies
- DataInsights
Super User
Try the Text Filter visual:
https://appsource.microsoft.com/en-us/product/power-bi-visuals/WA104381309?tab=Overview
I have tried the text filter visual but it seems that only searches over one table.
- DataInsights
Try this solution.
1. Create a calculated table for the columns you want to search:
SearchTable = DISTINCT ( UNION ( DISTINCT ( Table1[Email address] ), DISTINCT ( Table2[First Name] ), DISTINCT ( Table2[Last Name] ), DISTINCT ( Table3[Name] ) ) )2. Rename the column in this table to "Value".
3. Create measure:
Search Filter = IF ( MIN ( Table1[Email address] ) IN VALUES ( SearchTable[Value] ) || MIN ( Table2[First Name] ) IN VALUES ( SearchTable[Value] ) || MIN ( Table2[Last Name] ) IN VALUES ( SearchTable[Value] ) || MIN ( Table3[Name] ) IN VALUES ( SearchTable[Value] ), 1 )4. Add the measure [Search Filter] as a visual filter for each table visual (Search Filter is 1):
5. Using the Text Filter visual, search for a value:
