topic Re: Lookup and return value based on a condition - not related tables in DAX Commands and Tips

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

Anonymous — Thu, 17 Mar 2022 12:55:43 GMT

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.

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

amitchandak — Thu, 17 Mar 2022 13:06:14 GMT

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

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

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

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

Anonymous — Fri, 18 Mar 2022 10:40:50 GMT

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.

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

v-yanjiang-msft — Wed, 23 Mar 2022 06:26:25 GMT

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.

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

Anonymous — Wed, 23 Mar 2022 13:05:34 GMT

Hi,

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

Thanks for the help!