title: DAX lookup first non blank value in unrelated table
body: <p>How to lookup the first non blank value in <strong>unrelated</strong> table. I want an equivalent of: </p>
  
  <ul>
  <li>SQL <code>outer apply (select top 1 ...)</code> or </li>
  <li>Excel <code>VLOOKUP</code>.</li>
  </ul>
  
  <p>The DAX <code>LOOKUPVALUE</code> function as used in that code does not work.</p>
  
  <pre><code>LOOKUPVALUE(
        SearchTable[name]
      , SearchTable[id] -- how to get the fist value, if here are multiple id?
      , ThisTable[id]
      )
  </code></pre>
  
  <p>DAX <code>LOOKUPVALUE</code> function works fine, if only one unique value is returned as a result. The function <code>LOOKUPVALUE</code> returns error  message, if multiple values are found in the search table for one key:</p>
  
  <blockquote>
    <p>A table of multiple values was supplied where a single value was
    expected.</p>
  </blockquote>
  
  <p><a href="https://i.stack.imgur.com/BkANF.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/BkANF.png" alt="error message"></a>
  I do not want error message, I am happy with the top 1 result. </p>
  
  <p>Sample data file:</p>
  
  <p><a href="https://drive.google.com/uc?authuser=0&amp;id=1HdifDXc71GvIXVLCbdlmoycPtu0fxPis&amp;export=download" rel="nofollow noreferrer">DAX lookup top 1 value.pbix</a></p>
  
  <h2>Comment to accepted answer</h2>
  
  <p>I favor Alexis Olson's second approach the most, though I prefer <code>VAR</code> to <code>EARLIER</code> variation:</p>
  
  <pre><code>MyLookup = 
  var LookupKey = ThisTable[Product]
  return
  CALCULATE (
      MAX ( SearchTable[Category] ),
      SearchTable[Product] = LookupKey
  )
  </code></pre>
  
  <p>I accepted it though I have a strong feeling that this is not the best answer which may exist. The function returns <code>MAX</code> instead of <code>TOP 1</code> which is obviously additional effort.</p>
  
  <p>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. </p>
  
  <p>This question has a follow up question where I try to compare the queries in Dax Studio:</p>
  
  <p><a href="https://stackoverflow.com/q/59883211/1903793">How to check in Daxstudio which DAX query has better performance?</a></p>
  
  <h2>Update seeking further improvements</h2>
  
  <p>I wonder if it is possible to achieve a shorter query plan then the current winning answer. The winning code will be evaluated in <a href="https://daxstudio.org/" rel="nofollow noreferrer">DaxStudio</a> and should have shorter query plan then total 20 line of both physical and logical lines of query plan.</p>
  
  <pre><code>-- Bounty code
  EVALUATE
  ADDCOLUMNS (
      VALUES ( ThisTable[Product] ),
      "FilterLookup",
      VAR LookupKey = ThisTable[Product]
      RETURN
          CALCULATE ( MAX ( SearchTable[Category] ), SearchTable[Product] = LookupKey )
  )
  </code></pre>