IF and RELATED functions

[Bibeksharma]

I want to create a new column in Buildings table which gives:

• the country name, if it can find a related value in the Countries table; or
• Other otherwise.
  Buildings table is related with Countries table via Countries' code column and Buildings' CountryCode
  
  I am thinking something along the lines of formula shown below but i am not quite sure.
  

  IF (
  CONTAINS (RELATEDTABLE ([Countries]), Countries[Code]),
  
   
  
  "other"
  )

  
  
  Thank you.
  
  
  

[v-danhe-msft]

Hi @Bibeksharma,

If your two tables has one to one relationship by the code, you could try this formula:

Column = IF(ISBLANK(RELATED(Countries[Country])),"other",RELATED(Countries[Country]))

Result:

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/12k0ozlquoa8y76/IF%20and%20RELATED%20functions.pbix?dl=0

Regards,

Daniel He

[Bibeksharma]

Hi Daniel,
The tables have  one to many relationship. 

Thank you

[Anonymous]

Based on your description i'd be expecting something like this:

YourFieldsName = 
VAR countryCode = FIRSTNONBLANK(Countries[Code], Countries[Code])

RETURN
IF(
	ISBLANK(countryCode),
	"other",
	countryCode
)

[Bibeksharma]

Hi,

I get an error: Token Eof expected