IF statement question - Many to Many relationship

[Anonymous]

I'm trying to make a new column that tells me if a customer is new or old based on if their assigned ID exists in the table for old clients (Old) or not. But I get an error when making this column using the following code:

ISNEW = IF(ProjectFollowUpYTD[Customer] == 'Old'[Kundnummer], "NEW", "OLD")

The error I get is that it doesn't seem to find the table or column that I want to use in the IF statement.

Could this be because the relation between the tables is Many to Many?

Thanks

[Anonymous · Accepted solution]

Hi @Anonymous ,

Check the formula.

Column = IF(new[customer] in VALUES(old[customer]),"old","new")

 

Best Regards,

Jay

[parry2k]

@Anonymous Unfortunately you can add a column like that when using many to many, you should think of adding as a measure.

[Anonymous]

Thanks for the reply, 

Not quite sure how to write that measure, I can't seem to use the columns that I want. Seems I can only use measures with IF statements? 

I also tried using "LOOKUPVALUE" but I can't use Customer No as both search_columnName and search_value. I can't use a fixed search_value because I obviously want it to be dynamic and find all the records that exist in both tables, not just a specific customer. 

Just to try, I tried using a specific customer no, but I get an error saying: 

The column 'BefintligaKunder[Customer No]' either doesn't exist or doesn't have a relationship to any table available in the current context.

(BefintligaKunder is the table for old customers)