cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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
Highlighted
Microsoft
Microsoft

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

Highlighted
Helper I
Helper I

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

 

Highlighted
Helper I
Helper I

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
Helper I
Helper I

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

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors