title: DAX LOOKUPVALUE to return "Undefined" instead of blank?
body: <p>How to get an alternative result string message "Not found" or "Undefined", if the LOOKUPVALUE function does not find a match? The documentation of the <a href="https://docs.microsoft.com/en-us/dax/lookupvalue-function-dax" rel="nofollow noreferrer">LOOKUPVALUE</a> function mentions it is easily possible by using the fourth parameter. The fourth parameter is optional. But when I type it, I receive an error message. </p>
  
  <p>Here is why I need it. I want a Category of a product which has not been defined in a dictionary yet. It might be a classic scenerio. The dictionary table is updated manually with some lag and thus it does not contain all the unique products which pop up in FactTable. I want to solve it by Bridge table which will automate manual feeding of Dictionary. </p>
  
  <p><a href="https://i.stack.imgur.com/vGCSN.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/vGCSN.png" alt="enter image description here"></a></p>
  
  <p>I use the following <strong>Bridge</strong> table.</p>
  
  <pre><code>Bridge = 
      ADDCOLUMNS(
          DISTINCT(UNION(DISTINCT(FactTable[product]), DISTINCT(Dictionary[product])))
          , "FoundCategory"
          , LOOKUPVALUE(
                Dictionary[category]
              , Dictionary[product]
              , FactTable[product]
              --, "Undefined" -- Uncommenting this argument throws error
      )
  )
  </code></pre>
  
  <p><strong>Edit. After 2019 update of Power BI, this problem perished. It must have been a sort of a bug. The above code is working. Hurray!</strong></p>
  
  <p>How to force LOOKUPVALUE function to return <code>Undefined</code> string value instead of <code>blank()</code>?</p>
  
  <p>I can think of this:</p>
  
  <pre><code>Bridge = 
  ADDCOLUMNS(
      DISTINCT(UNION(DISTINCT(FactTable[product]), DISTINCT(Dictionary[product])))
      , "FoundCategory"
      , IF(ISBLANK(
          LOOKUPVALUE(
            Dictionary[category]
          , Dictionary[product]
          , FactTable[product])
          )
          ,"Unmapped"
          ,LOOKUPVALUE(
            Dictionary[category]
          , Dictionary[product]
          , FactTable[product])
          )
  )
  </code></pre>
  
  <p>However I wonder if it does not calculate the LOOKUPVALUE twice. If so, what might be more efficient way?</p>
  
  <p>Here are the tables if you would like to recreate the problem.</p>
  
  <p><strong>FactTable:</strong></p>
  
  <pre><code>let
      Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSiwoyElVitWJVkpKzANCMLMgvySxJF8pNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [product = _t])
  in
      Source
  </code></pre>
  
  <p><strong>Dictionary:</strong></p>
  
  <pre><code>let
      Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSiwoyElV0lFKKyrNLFGK1YlWKsgvSSzJBwqVpaanliQmAaVjYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [product = _t, category = _t])
  in
      Source
  </code></pre>