Forum Discussion

Gajo's avatar
Gajo
Helper I
8 years ago

Using linked data in calculated columns

I have two tables: one with Product data and one with specifications. In the table with product data I have created two extra columns with the Lower and Upper Spec from the spec file with the limits...
Need Help
Tips and Tricks
Gajo's avatar
Gajo
Helper I
8 years ago

Is there anyone who can help me with this problem/question?

 

PS: the formula should in the end be something like NV = ( 2 * V - (USL+LSL) ) / (USL-LSL)

Gajo's avatar
Gajo
Helper I
8 years ago

After lots of attempts I happened to stumble onto:

 

NormalizedValue = (2 * '1 0 VerOutput'[ValueFiltered] - (LOOKUPVALUE('1 0 SpecOutput'[USLFiltered],'1 0 SpecOutput'[Link],'1 0 VerOutput'[Link]) + LOOKUPVALUE('1 0 SpecOutput'[LSLFiltered],'1 0 SpecOutput'[Link],'1 0 VerOutput'[Link])) ) /
(LOOKUPVALUE('1 0 SpecOutput'[USLFiltered],'1 0 SpecOutput'[Link],'1 0 VerOutput'[Link]) - LOOKUPVALUE('1 0 SpecOutput'[LSLFiltered],'1 0 SpecOutput'[Link],'1 0 VerOutput'[Link]))

 

LOOKUPVALUE seems to do what I needed after I filtered out any non number values

 

It's to bad that this forum seems to be unable to help with anything but the most basic of questions....