cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Gajo Frequent Visitor
Frequent Visitor

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

image.png

 

 

4 REPLIES 4
v-ljerr-msft Super Contributor
Super Contributor

Re: Using linked data in calculated columns

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? Smiley Happy

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

 

Regards

Gajo Frequent Visitor
Frequent Visitor

Re: Using linked data in calculated columns

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 Frequent Visitor
Frequent Visitor

Re: Using linked data in calculated columns

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

 

image.png

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

Highlighted
Gajo Frequent Visitor
Frequent Visitor

Re: Using linked data in calculated columns

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