Multiple If Statement with 2 tables

daytronxllc · ‎10-24-2018

Hi,

Below is a half attempt of what I am trying to accomplish, I am looking to see if anyone can help.

I have 2 tables and they are related like this based on the column:

InterimWorkingProject.InterimWorkingProjectIdent = ProjectCustomField.ProjectIdent

What we are a tax firm and need to look in a column that contains text like this: 2017 1040 or 2018 1041, I have made a conditional column in query editor and called is "Return" that looks for text and returns, 1040 or 1041 to scrub out any other text, so that is working fine. What I need to do now is basically a big nested if statement. I can't seem to call columns in a measure. Can you help?

IF InterimWorkingProject.Return contains 1040 or 1041 or 990 or 1120H
&& ProjectCustomField.ProjectCustomFieldValue is '00000000-0000-0000-0000-000000000000' OR '1bee2293-a7bf-42f1-af4c-b2bc59283ec9'
then 120.00

Else

IF InterimWorkingProject.Return contains 1120 or 1120S or 1065
&& ProjectCustomField.ProjectCustomFieldValue is '00000000-0000-0000-0000-000000000000' OR '1bee2293-a7bf-42f1-af4c-b2bc59283ec9' then 145.00

Else

IF InterimWorkingProject.Return contains 1120 or 1120S or 1065 or 1040 or 1041 or 990 or 1120H
&& ProjectCustomField.ProjectCustomFieldValue is '1ac0e098-75b9-4da5-aa29-85a970fbb3e4'
then "NC Staff"

Else

IF InterimWorkingProject.Return contains 1120 or 1120S or 1065 or 1040 or 1041 or 990 or 1120H
&& ProjectCustomField.ProjectCustomFieldValue is 'a213a32d-89f7-4272-bc5b-efa15a4b684f'
then "NC - Client Child"

Else

IF InterimWorkingProject.Return contains 1120 or 1120S or 1065 or 1040 or 1041 or 990 or 1120H
&& ProjectCustomField.ProjectCustomFieldValue is '5634fe00-679a-4021-8485-2bbe6684c65d'
then "NC - Staff Immediate"

Anonymous · ‎10-24-2018

If you want to use IF statement in a measure, you have to use a measure as its parameter. For your case, you can create a measure and call VALUES to get the value from InterimWorkingProject.Return. Then use it in your IF statement.