Lookupvalue function returning BLANK

[yanivshe]

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_RESULT
...	...	...
IPHONE	...	97.87%
...	...	...
CAMERA	...	93.25%
GPS	...	98.45%
GYRO	...	96.01%

Table "PRODUCT_TREE": contains product and it's product childrens.

PARENT	CHILD	QTY
...	...	...
IPHONE	GPS	1
IPHONE	CAMERA	2
IPHONE	GYRO	1
...	...	...

So for example an IPHONE is composed of 1 GYRO, 1 GPS & 2 CAMERAS

The relationship between the tables is 1 to many on the PRODUCT_LINE & PARENT (1 product contains many parents) PRODUCT_LINE [1] <-> [*]PARENT (PRODUCTS table filters PRODUCT_TREE)

I need to get the TEST_RESULT of a CHILD in the PRODUCT_TREE table for later use.

On the PRODUCT_TREE table I added a new column called TEST_YIELD like so:

PARENT	CHILD	QTY	TEST_YIELD
IPHONE	GPS	1	=QTY*GPS_TEST_RESULT=1*98.45%
IPHONE	GYRO	1	=QTY*GPS_TEST_RESULT=1*96.01%
IPHONE	CAMERA	2	=QTY*CAMERA_TEST_RESULT=2*93.25%

For the TEST_YIELD column I used the following column DAX:

TEST_YIELD =
var childYield = LOOKUPVALUE(PRODUCTS[TEST_RESULT], PRODUCTS[PRODUCT_LINE], PRODUCT_TREE[CHILD], 0.0)
return PRODUCT_TREE[QTY]*childYield

This return 0.

I dont know why I am getting 0?

Is it related to the relations between the tables?

Is it a bug in Power BI?

Did anyone encouterd such behavior?

Any idea how to resolve this?

Thanks in advance

[v-kelly-msft · Accepted solution]

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]

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]

Thanks, this worked for me on May 2023!

[edhans]

See my file here. Your calculated column seems to work for me as shown below, correct? It may be your relationship isn't set up correctly. See the model in my file and ensure yours is the same.