find the Text values which contains in columns .

[rambabukm]

hi, i want to add a values based on the column witch contains values. Ca you please check the bellow quer, using this need to add in powerbi.

Ex:

IF CONTAINS([Name], 'AA') THEN 'EMEA'
ELSEIF CONTAINS([Name], 'AZ') THEN 'EMEA'
ELSEIF CONTAINS([Name], 'AW') THEN 'APAC'
ELSEIF CONTAINS([Name], 'AR') THEN 'EMEA'
ELSEIF CONTAINS([Name], 'AY') THEN 'EMEA'
ELSEIF CONTAINS([Name], 'AI') THEN 'AMER'
ELSEIF CONTAINS([Name], 'AQ') THEN 'APAC' END

[erik_tarnvik · Accepted solution]

Instead of creating a formula for this purpose, I would suggest that you create a table with the country-region mapping and bring that table into your model and define a relationship between your fact table and the mapping (dimension) table. This would be much easier, it will be a very long if statement otherwise.

[Eric_Zhang · Accepted solution]

---

@rambabukm wrote:

hi, i want to add a values based on the column witch contains values. Ca you please check the bellow quer, using this need to add in powerbi.

Ex:

IF CONTAINS([Name], 'AA') THEN 'EMEA'
ELSEIF CONTAINS([Name], 'AZ') THEN 'EMEA'
ELSEIF CONTAINS([Name], 'AW') THEN 'APAC'
ELSEIF CONTAINS([Name], 'AR') THEN 'EMEA'
ELSEIF CONTAINS([Name], 'AY') THEN 'EMEA'
ELSEIF CONTAINS([Name], 'AI') THEN 'AMER'
ELSEIF CONTAINS([Name], 'AQ') THEN 'APAC' END

---

@rambabukm

What values are in the column Name in your case? If they're simply "AA","AZ" and etc, you can follow @erik_tarnvik suggestion to create mapping table and create proper relationship.

Otherwise, you could try below DAX formula.

Column = 
SWITCH(TRUE(),SEARCH("AA",'Table'[Name],1,0)>0||
			  SEARCH("AZ",'Table'[Name],1,0)>0||
			  SEARCH("AR",'Table'[Name],1,0)>0||
			  SEARCH("AY",'Table'[Name],1,0)>0,
			  "EMEA",
			  SEARCH("AW",'Table'[Name],1,0)>0||
			  SEARCH("AQ",'Table'[Name],1,0)>0,
			  "APAC",
			  SEARCH("AI",'Table'[Name],1,0)>0,
			  "AMER")

[erik_tarnvik · Accepted solution]

Instead of creating a formula for this purpose, I would suggest that you create a table with the country-region mapping and bring that table into your model and define a relationship between your fact table and the mapping (dimension) table. This would be much easier, it will be a very long if statement otherwise.