Using columns from different tables in a visual table

[garuth01]

I have created 2 tables within Power Bi. For both tables: product & position are the same. 'Reference' has a column called Measurement, which represents the different types of measurements to be
taken; 'table1' has a column for each one of the measurement types (A,B,C,D) and it lists actual recorded data for these measurement types while at certain positions (L1, L2, L3, L4, L5). 'Reference' also has upper limit values for each measurement type at each position. 
What I want to do is have new columns called resultA, resultB, resultC, and resultD. So if I was trying to make column resultA, I would want it to print "PASS" if the value under the A column was less than the
value in the upper limit column. If it was greater than or equal to that upper limit value, It should print "FAIL"

I want to make a visual table that has [upper limit], [A], and [resultA], but it isn't letting me. It will do all blanks for one column, print all "PASS", or print all "FAIL."

If Power Bi isn't capable of doing what I want, please help me find a loophole of some sort. I'm really stuck on this one. 

[garuth01 · Accepted solution]

So what I did was manage the relationships so that the columns which where in both tables would have a relationship. 

                                                    I'd say they are working properly, but I encountered another issue when trying to reach my end goal. 

I wanted to somehow have a final results column somewhere on the page sort of like the one below (that one doesn't work though)Basically what I want to do is compare resultA, resultB, resultC, and resultD and if they are all "PASS" I want the resultFINAL column to output "PASS" 

[alexvc]

Hi there,

What you are trying to do can be done as long as your tables have the correct structure and relationship between them. What I see is that neither table has unique values, also in your table 1 you have A,B,C,D measurements as columns.

One approach is to create two tables, one for Positions and other one for Products. This is convenient if you are going to be adding more of those records in the future. You can then create relationship between your reference and Table 1 tables to these two tables.

A quick alternative is to create a calculated column concatenating Product&Position&Type of Measure in both tables, then you can create a relationship with these columns without creating more tables. But for this you should have Type of Measure as rows and not columns in your table 1

Let me know if it works

Alejandro