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

Siddharth Thanga Mariappan Siddharth Thanga Mariappan · Accepted Answer · 2019-07-17T12:56:37

You can use an if else loop inside that with something like this :-

Bridge = 
    ADDCOLUMNS
   (
    DISTINCT(UNION(DISTINCT(FactTable[product]), DISTINCT(Dictionary[product]))),
    "FoundCategory",
    IF(
          LOOKUPVALUE(
          Dictionary[category],
          Dictionary[product],
          FactTable[product]) = "", 
          "Undefined", 
          LOOKUPVALUE(
          Dictionary[category],
          Dictionary[product],
          FactTable[product]))
    )

Or you could do something like this,

Bridge = 
        DISTINCT(UNION(DISTINCT(FactTable[product]), DISTINCT(Dictionary[product])))

Then a calculated column:-

foundCategory = 

var look_up_value = LOOKUPVALUE(
          Dictionary[category],
          Dictionary[product],
          Bridge[product])

var out = IF(look_up_value = BLANK(), "undefined", look_up_value)

return out

DAX LOOKUPVALUE to return "Undefined" instead of blank?

2 Answers