DAX lookup first non blank value in unrelated table

Question

How to lookup the first non blank value in unrelated table. I want an equivalent of:

• SQL outer apply (select top 1 ...) or
• Excel VLOOKUP.

The DAX LOOKUPVALUE function as used in that code does not work.

LOOKUPVALUE(
      SearchTable[name]
    , SearchTable[id] -- how to get the fist value, if here are multiple id?
    , ThisTable[id]
    )

DAX LOOKUPVALUE function works fine, if only one unique value is returned as a result. The function LOOKUPVALUE returns error message, if multiple values are found in the search table for one key:

A table of multiple values was supplied where a single value was expected.

I do not want error message, I am happy with the top 1 result.

Sample data file:

DAX lookup top 1 value.pbix

Comment to accepted answer

I favor Alexis Olson's second approach the most, though I prefer VAR to EARLIER variation:

MyLookup = 
var LookupKey = ThisTable[Product]
return
CALCULATE (
    MAX ( SearchTable[Category] ),
    SearchTable[Product] = LookupKey
)

I accepted it though I have a strong feeling that this is not the best answer which may exist. The function returns MAX instead of TOP 1 which is obviously additional effort.

The sample data now contains 4 working solutions. I have numbered them according to my preferences. The first two are by Alexis Olson. As far as I could learn from DAX Studio, both solutions are similarly fast and have a simple execution plan. The second solution is the only solution that performs just two table scans (all other approaches have 3 scans). The third solution has a complicated execution plan, and during the third scan it passes through the entire search table, regardless of whether it is a million rows.

This question has a follow up question where I try to compare the queries in Dax Studio:

How to check in Daxstudio which DAX query has better performance?

Update seeking further improvements

I wonder if it is possible to achieve a shorter query plan then the current winning answer. The winning code will be evaluated in DaxStudio and should have shorter query plan then total 20 line of both physical and logical lines of query plan.

-- Bounty code
EVALUATE
ADDCOLUMNS (
    VALUES ( ThisTable[Product] ),
    "FilterLookup",
    VAR LookupKey = ThisTable[Product]
    RETURN
        CALCULATE ( MAX ( SearchTable[Category] ), SearchTable[Product] = LookupKey )
)

Answer 1

Try this:

Column =
VAR row_id = ThisTable[id] 
VAR ret_val =
    SELECTCOLUMNS (
        TOPN (
            1;
            CALCULATETABLE (
                SearchTable;
                SearchTable[id] = row_id;
                SearchTable[name] <> BLANK ()
            )
        );
        "name"; SearchTable[name]
    )
RETURN
    ret_val