Dashboard with one search filter against multiple table matrices

[AMS22]

In leui of an inventory management system, I am attempting to build a Power BI dashboard to enable us to search a name across multiple Excel files with multiple sheets within. My vision is to have one search bar at the top of my dashboard with multiple matrices of results from different tables, e.g., laptop results, peripheral results, mobile handsets results, mobile numbers results. 

I've learned how to create a calculated field within a table to improve my search results as the naming convention between documents aren't consistent (e.g. some tables have columns username, first name, last name, other tables have just a full name field...) but I've not yet worked out how to get one filter to show results for multiple tables. I am using the Text Filter visual downloaded from the Microsoft app store. 

We have an inventory management system on the way (soon™ since 2021...) but I need a way to query users faster than opening four different spreadsheets!

Am I missing a trick? 

[djurecicK2]

Hi @AMS22 ,

 That DAX looks good. Not quite sure about your comment- are you expecting the result to be 2 columns?

[AMS22]

Yes, I think it needs to pull through all the different columns that I'm trying to bring in so that I can create references from the columns to their original tables to be able to search. 

[AMS22]

Hi @djurecicK2 - thank you for your reply. I've spent some time looking at distinct table tutorials online but I'm not having much luck!

For example if I enter the formula Table = DISTINCT(UNION(VALUES('Active Assets'[AD Username]),VALUES('Equipment Exceptions'[User Name]))) as advised by tutorials, it's not actually pulling through the two different columns to search against:

I couldn't get the formula to accept without UNION. 

The dashboard is so close to being done and saving us all a lot of time, I'm definitely missing something obvious!

[djurecicK2]

Hi @AMS22 ,

 You could try to create a distinct name table with the distinct names from each file. Then join the files to the distinct name table and use that as your filter.