Report - Filter with Multiple Criteria?

[Will_Texas]

Hello, I am trying to create a Report Table of the top 5 closest comparable records based on multiple search criteria.


The data is currently in a Table format, using data Power Query. (.xls file example attached)


I would like to limit my search results to a specific count (ie. 5 in this example).


I am searching based on Matching Text across multiple columns, and numerical Values that are greater/less than.


Approach:
Filter() I thought might work, but I figure out how to limit the # of records returned (ex. to 5). Additionally with Filter(), I can't easily Sort the results, (ex by Year, descending) like in Table or Array. Plus this Filter() function would be enormous if I added more columns to evaluate, etc.


Slicers might work, but I can't seem to also easily incorporate a numerical filter based on user input (ex. >$100,000)


Any Suggestions?

 

[Will_Texas]

Hello all - I put together the attached worksheet example. For context its comparing different real estate records.

Basic Steps in the process:
I have two tables of Data. Table 1 is a set of availble properties to select.
Table 2 is a Set of properties that have a Distance formula calculted to show those in the closes proximity (X Miles) from Subject record.

I need to create a new Table, that selects a specified number of records (ex. 5 records) from Table 2, based on a series of user criteria that is entered (matching text, and evalauting numerical values (>, <, etc.).

Options I've looked at:
Filter() & XLookup - But I can't simplistically apply Sort Criteria by Level(s)
Pivot Table - but can't specify User entered criteria
Index(Match, Match)

Any Suggestions? The Step 2 Output table is where I'm looking to create this Output table.

 

[Ed Kelly]

Will, DAX and M code will solve virtually all of this. Would suggest you consider microsoft Power BI to do the same as you can build a very sophisticated model with cross visual functionality, TopN, mapped locations, parameter analysis etc. you can do a fair amount of it in excel it is just going to be clunky and uninspiring.