If value in colum A=x, table 1 then pick the value from column B, table 2

[eugen13]

Hello,

How can I create a new column that pick the value from table 2 based on the value from another column from the table 1?

If value in colum A=x, table 1 then pick the value from column B, table 2, if A=y, table 1, then pick value from column C, table 2

In my case, if a materiale/object is measured in m2(squared meters) then pick the area from table 2, if its measured in m3 then pick the volume. 

[v-kongfanf-msft · Accepted solution]

Hi @eugen13 ,

You seem to have created this formula in measure, which is causing the error. Please try again in the calculated column.

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[eugen13]

Hi,

Sorry for not responding that long.

I created it with New  Column and still have the same issue.

[v-kongfanf-msft]

Thanks for the reply from @jgeddes , please allow me to provide another insight:

Hi @eugen13 ,

You can try formula like below:

New Column1 =
VAR MaterialType = 'Table 1'[Material Type]
RETURN
    IF (
        MaterialType = "m2",
        RELATED ( 'Table 2'[Area] ),
        IF ( MaterialType = "m3", RELATED ( 'Table 2'[Volume] ), BLANK () )
    )

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[eugen13]

Hi,

I tried it and I think it should work, but I get this erorr:
A single value for column 'Unitate de masura' in table 'Lucrari' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

**What should I use instead of RELATED if the tables doesnt have direct relations.?They are connected through another table.

I tried the following formula:

[jgeddes]

You may be able to use the LOOKUPVALUE function.
DAX - LOOKUPVALUE 

[eugen13]

I think this it's not what I am looking for.
I dont have the same column A in the table 2, to be able to match the column B. Its an overall condition. 

I will attach a photo, i hope it will be more understandable. 

[eugen13]

Nope, just posted

Im waiting for help

[isfarhassan]

Were you able to solve this problem? How did you solve it?