Forum Discussion

nrowey's avatar
nrowey
Helper I
3 years ago

IF statement between two joined tables on multiple values

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))
Need Help

3 Replies

  • Shaurya's avatar
    Shaurya
    Memorable Member
    3 years ago

    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's avatar
      nrowey
      Helper I
      3 years ago
      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