DAX LOOKUPVALUE to return "Undefined" instead of blank?

Question

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 LOOKUPVALUE 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.

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.

I use the following Bridge table.

Bridge = 
    ADDCOLUMNS(
        DISTINCT(UNION(DISTINCT(FactTable[product]), DISTINCT(Dictionary[product])))
        , "FoundCategory"
        , LOOKUPVALUE(
              Dictionary[category]
            , Dictionary[product]
            , FactTable[product]
            --, "Undefined" -- Uncommenting this argument throws error
    )
)

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!

How to force LOOKUPVALUE function to return Undefined string value instead of blank()?

I can think of this:

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])
        )
)

However I wonder if it does not calculate the LOOKUPVALUE twice. If so, what might be more efficient way?

Here are the tables if you would like to recreate the problem.

FactTable:

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

Dictionary:

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

Answer 1

One other way to do this is to use the LOOKUPVALUE functions alternative value field. This allows you to specify a value returned instead of blank, thus saving having to do a check to see if it is blank.

LOOKUPVALUE in Microsoft's online documentation on the function.

Edit: Did a quick test in Power BI Desktop and was able to get alternate values to work in a calculated column to an existing model.

LOOKUPVALUE(
          Dictionary[category]
        , Dictionary[product]
        , FactTable[product])
        , "Undefined"
)