DAX problem - The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value

I have three tables, childjobs, jobfinishdates and jobprepdates. I have confirmed that both the jobfinishdates and jobprepdates have unique jobnumbers and there are no duplicates. There is a one to one relationship between ChildJobs and jobfinishdates and jobprepdates

However when i try to add the prep or finish date to the childjobs table I am met with "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value." and I don't know how to correct it.

The formula I am using to add prepdates to the childjobs table is

PrepDate = 
ADDCOLUMNS(
    ChildJobs,
    "Prep Date",
    LOOKUPVALUE(
        JobPrepDates[PrepDate],
        JobPrepDates[Job Number],
        [Job Number]
    )
)

and I am expecting it to add the prepdate from the JobPrepDates table but keeps giving me this error and filling the column with #ERROR

ADDCOLUMNS DAX function adds a column to a table and returns a table, which is fine for a Calculated Table. Measures and Calculated Columns can only return a scalar value (ie a single value, not tables/rows).

As you are adding a Calculated Column, your formula would simply be:

Prep Date =
  LOOKUPVALUE(
    JobPrepDates[PrepDate],
    JobPrepDates[Job Number],
    [Job Number]
  )

You could also use RELATED function since you have a relationship setup, instead of LOOKUPVALUE. Example:

Prep Date = RELATED(JobPrepDates[PrepDate])

From the LOOKUPVALUE documentation:

Remarks

• If there is a relationship between the table that contains the result column and tables that contain the search columns, in most cases, using the RELATED function instead of LOOKUPVALUE is more efficient and provides better performance.