IF statement between two joined tables on multiple values

[nrowey]

I have two joined tables, I want to create a new column containing a value from one table based on a colum value in the other.

e.g   based on table COA1 colum name "accounttype" evaluating to a "S" or a "C" , I want the value from table GLDetail column name "postingamount"   to be added to the new column, if evaluation is not "C" or "S" than 0

Tried this and a bunch of others but no luck...

Gross1 = if(COA1[ACCOUNTTYPE]="C" or "S",SUM(GLDetail[postingamount]),0))

[Ashish_Mathur]

Hi,

Share some data to work with and show the expected result.

[Shaurya]

Hi @nrowey,

You can use:

Gross = IF('COA1'[AccountType] = "C" || 'COA1'[AccountType] = "S",
LOOKUPVALUE('GLDetail'[PostingAmount], 'GLDetail'[JoinColumn], 'COA1'[JoinColumn]), 0)

Mark this post as a solution if that works for you!
Consider taking a look at my blog: Forecast Period - Previous Forecasts

[nrowey]

Statement I entered and error result below.

thance for any guidence 

Gross = IF('COA1'[AccountType] = "C" || 'COA1'[AccountType] = "S",

LOOKUPVALUE('GLDetail'[PostingAmount], 'GLDetail'[Match], 'COA1'[HOSTITEMID]), 0)

error:

A single value for column 'HOSTITEMID' in table 'COA1' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result