Lookup and return value based on a condition - not related tables

[Anonymous]

Hi,

I have a table (project part) containing the following:
project number | project part number | Project part name | Stage code

I want to add a new column to this that shows when the project part number was at stage code 40, with the period number as the value. This data can be retrieved from another table (name: stage) that has no relationship with the project part table. I also can't create a relationship between these two tables due to ambiguity.

So my table should eventually look like this:

project number	project part number	Project part name	Stadium code	Period number closure
70220100	2	Test1	40	202201
70220102	4	Test2	50	202202

I've tried everything with LOOKUPVALUE and CALCULATE but I can't figure it out.

[Anonymous · Accepted solution]

Hi,

I have found the solution via filters. So there was no formula necessary.

Thanks for the help!

[v-yanjiang-msft]

Hi @Anonymous ,

According to your description, I create a sample.

This is Project part table.

This is Stage table.

In my understanding, you want to create a column in the Project part table, when the Stadium code is 40, it should be 4 in my sample. Here's my solution.

Column =
IF (
    'Project part'[Stadium code] = 40,
    LOOKUPVALUE ( Stage[Period number], Stage[Stadium code], 40 )
)

Get the result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[Anonymous]

Hi,

This didn't work. I get a error with:
the column period no. in the table d_period was not found or cannot be used in this expression.

My relations are as follow:

There is no direct relation between d_stadium and d_periode. I can't create a relationship between these two tables due to ambiguity.

Hopefully this makes it clearer what the problem is.

[amitchandak]

@Anonymous , You can get value like . Table may not be related

maxx(filter(Table2, Table2[Code] = table1[Code] ) , Table2[Value])

or

sumx(filter(Table2, Table2[Code] = table1[Code])  && Table2[col] ="A"), Table2[Value])

refer 4 ways to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8