Forum Discussion

yanivshe's avatar
yanivshe
Icon for Helper I rankHelper I
6 years ago
Solved

Lookupvalue function returning BLANK

Hi all,   I am breaking my head over this one... I have 2 table with products and their test results: Table "PRODUCTS": contains products and metadata and test results PRODUCT_LINE ... TEST...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    Hi  yanivshe ,

     

    Is there multiple rows have the same value in the column "Child"?If so,you'd better use relatedtable() instead of lookupvalue() and you need to create a calculated column as below:

     

     

    Column = 
var a= CALCULATE(MAX('Table'[TEST_RESULT]),FILTER(RELATEDTABLE('Table'),'Table (2)'[CHILD]='Table'[PRODUCT_LINE]))
Return
'Table (2)'[QTY]*a

     

     

    Finally,you will see:

     

    For the related .pbix file,pls click here.

     

     
    Best Regards,
    Kelly
     
    Did I answer your question? Mark my post as a solution!

     

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
6 years ago

You are getting zero because LOOKUPVALUE is returning the alternate value that you specified of zero. This is because LOOKUPVALUE cannot filter down to a single row. When multiple rows match the given criteria, LOOKKUPVALUE returns its alternate value, which by default is BLANK.

 

So, for whatever reason, you have multiple values being returned by LOOKUPVALUE. If you want to see what they are, do this:

 

TEST_YIELD_RESULTS =
  CONCATENATEX(
    FILTER(PRODUCTS,PRODUCTS[PRODUCT_LINE] = PRODUCT_TREE[CHILD]),
    [TEST_RESULT]
  )

 

Depending on your situation, you could use MAXX with the same filter used above in CONCATENATEX in place of LOOKUPVALUE or you could specify additional search criteria to ensure you get a single row returned.

 

JonathanSCAlor's avatar
JonathanSCAlor
Frequent Visitor
3 years ago

Thanks, this worked for me on May 2023!