Finding ID and checking if completed or not

[Xandman]

Hi

I have 2 tables, one is a record of all the received work and another table is all the completed work. Both are linked to a ID list. I can't combine them since some columns do not exist on both.

                                      Received Table                                                                    Completed Table

ID List                            Date             Received ID           Person                               Date       Completed ID       Person

1                                  1/1/2021             1                            A                                   1/2/2021       1                       A

2                                  1/5/2021             2                            B                                   1/10/2021       3                     C

3                                  1/10/2021           3                            B                                   

I'm trying to find IDs of received work that exist on the completed table and see who completed them. I'm also trying to find IDs of received work but not completed and who was assigned to it. Also, trying to find the number of days between received and completed.

If an ID is not completed, you will find the assigned person on the Received Work Table and if it was completed, you will find the person who completed it on the Completed Table.

[Fowmy · Accepted solution]

@Xandman 

You can create three columns in the Received Tables as follows:

Completed Person = LOOKUPVALUE( Completed[Person] , Completed[Completed ID] , 'Received'[Received ID])

Non Completed Person = 
IF( ISBLANK(LOOKUPVALUE( Completed[Person] , Completed[Completed ID] , 'Received'[Received ID])), 'Received'[Person] )

Days to Complete = 
var __date = 
LOOKUPVALUE( Completed[Date] , Completed[Completed ID] , 'Received'[Received ID])
return
IF( __date <> BLANK() , DATEDIFF( 'Received'[Date] , __date , DAY )+1
)

[Xandman]

Thank you again! I didnt realize it could be done this way.