Using linked data in calculated columns

[Gajo]

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 for for the test in that line.

Now I'd like to normalize the Value based on it's specification limits. 

Unfortunately Power BI will not allow me to do this. What I tried as a first step is something  like: 

NormalizedValue = '1 0 VerOutput'[Value] - '1 0 SpecOutput'[USL]

Can anyone help me with some sort of workaround?

[v-ljerr-msft]

Hi @Gajo,

Is there any relationship between the two tables? Could you post your table structures with some sample/mock data and the expected result, so that we can better assist on this issue?

In addition, could you try using the formula below to create a new measure to see if it works in your scenario? 

NormalizedValue =
CALCULATE ( SUM ( '1 0 VerOutput'[Value] ) )
    - CALCULATE ( SUM ( '1 0 SpecOutput'[USL] ) )

Regards

[Gajo]

Hi,

Both tables have columns with product and measurment name that are used to link the two tables. The spec table contains in each line a single specification for a single measurement of a single product. The data table has a single measurment result in every line.

Using this link the data table has two additional columns with specification limits. (Both tables have data for several products and measurments)

Each line of the datatable has the limits that are matched to the data point of that line using the names of product and measurment as a link between the tables. Using operators like sun or average will mix all the specs of various products and can therfor not be used. 

My idea is to create a table with in column one the dat from the data table, column 2/3 are the specifications where each line is filled with the values from the spectable that go with the data value in the first column. In coulumn 4 there now should be a 'normalized' version of the data from column 1 using the specs in column 2/3. Each line will therefore use a seperate conversion based on the specs of that line/measurment.

I hope that makes more clear what I need to do here....

Kind regards,

Gajo

[Gajo]

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]

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....