Forum Discussion

Anika3186's avatar
Anika3186
Frequent Visitor
5 years ago
Solved

need help with a measure Issue

Hello,

I have a table with two columns ("City" and "country"). Now, I want to create a new column "Result". I'm looking for an expression that says:

 

If column country = "Other" and column City contains the word "Munich" then write "Germany", If country = "Other" and City contains the word "Cologne" then write "Germany" otherwise take on column "country".

 

Below is my expected output

CitycountryResult
Munich and DortmundOther Germany
Bremen and CologneOther Germany
Berlin and HamburgGermanyGermany
London and ManchesterOther Other
Rom and FlorenzOther Other

 

Can somebody help me?

Thanks in advance!

Anika

  • amitchandak's avatar
    amitchandak
    5 years ago

    Anika3186 , Create a new column result like

     

    Switch ( True(),
    [country] = "Other" && search("Munich",[City],,0) >0 , "Germany",
    [country] = "Other" && search("Cologne",[City],,0) >0 , "Germany",
    [country]
    )

2 Replies

  • littlemojopuppy's avatar
    littlemojopuppy
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi Anika3186 

     

    Try this...

    IF(
	AND(
		[Country] = "Other",
		OR(
			NOT(ISBLANK(FIND("Munich", [City])),
			NOT(ISBLANK(FIND("Cologne", [City]))
		)
	),
	"Germany",
	[Country]
)
  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anika3186 , Create a new column result like

     

    Switch ( True(),
    [country] = "Other" && search("Munich",[City],,0) >0 , "Germany",
    [country] = "Other" && search("Cologne",[City],,0) >0 , "Germany",
    [country]
    )