Forum Discussion
LOOKUPVALUE blank value
I have a calculated column in TableX with a hard coded lookup from table Customers. I cannot think of any reason why it would differ between rows, as it is not actually based on any row context. Yet, it returns blank (or alternate value if specified) for some rows. Testing with the following:
Column = LOOKUPVALUE( Customers[Customer Name], [Customer ID], "10017952" )
Is there any reason for this to be happening?
7 Replies
Hi hansei,
i think the reason is "Context Transition"
https://dax.guide/lookupvalue/
Internally LOOKUPVALUE generates this equivalent syntax:
VAR SEARCHVALUE = <SEARCH_VALUE> RETURN CALCULATE ( SELECTEDVALUE ( <RESULT_COLUMNNAME>, <ALTERNATE_RESULT> ), FILTER ( ALLNOBLANKROW ( <SEARCH_COLUMNNAME> ), <SEARCH_COLUMNNAME> == SEARCHVALUE -- THE == OPERATOR DISTINGUISHES BETWEEN BLANK AND 0/EMPTY STRING ), ALL ( <TABLE_OF_RESULT_COLUMNNAME> ) -- IF RESULT_COLUMNNAME IS T, THIS IS ALL ( T ) )If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
mwegenerthanks for the information. However, I am unsure how context transition would actually affect the calculation - there is no direct relationship between the TableX and Customers (e.g. typing RELATED( does not offer Customers as an option). Is the implication that the row context is filtering Customers in such a manner that the LOOKUPVALUE cannot find a match within that context?
