topic Re: If statement with two tables that are related in DAX Commands and Tips

If statement with two tables that are related

Henry44 — Tue, 08 Jun 2021 09:45:55 GMT

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!

Re: If statement with two tables that are related

Tanushree_Kapse — Tue, 08 Jun 2021 12:22:16 GMT

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!

Re: If statement with two tables that are related

Henry44 — Wed, 09 Jun 2021 10:32:17 GMT

Hi Tanushree_Kapse

Thank you so much, all sorted now. 😁