If statement with two tables that are related

[Henry44]

Hello

I have two tables that are related (they are related by a Job number)

Authority GIS (with a field called authority)  and SAW (with a field called Location_of_job)

I've been trying to do the following IF statement to create a new column with a job location.  I want a new column in the Authority table which contains the authority field value from Authority GIS if is not 'Not-recorded' but contains the Location_of_job value from the SAW table if it is 'Not-recorded'.  

Job_Location = IF(AUTHORITY <> "Not_recorded", AUTHORITY, Location_of_job) this isn't working and the error message is Cannot find name 'Location_of_job'

Any idea what I'm doing wrong?  Does this not work with two tables?

Thank you!

[Tanushree_Kapse · Accepted solution]

Hi @Henry44 ,
You must have the Job_location column in the Authority table. For this you can bring in this column from the SAW table using LOOKUPVALUE Function, and then try using the DAX (Job Location field must be from Authority table).
 

Create column in AUTHORITY:
Job_Location= LOOKUPVALUE(Location_of_Job, Authority(Job number), SAW(Job number))
Once this column is created, you can use your DAX:
Job_Location2 = IF(AUTHORITY <> "Not_recorded", AUTHORITY, Job_Location)

I hope this helps!

[Henry44]

Hi @Tanushree_Kapse 

Thank you so much, all sorted now.  😁