Custom Column's Query showing Discrepancy

[utsavlexmark]

Hello,

Let me share the data first

Country                        Email Name

Germany                      EMEA_CHAN_PN_Newsletter_EMAIL_Q3_08_2021_de_AT_BSD
Germany                      EMEA_CHAN_PN_Newsletter_EMAIL_Q3_08_2021_de_AT_HW
Germany                      EMEA_CHAN_PN_Newsletter_EMAIL_Q3_08_2021_de_CH_BSD
Germany                      EMEA_CHAN_PN_Newsletter_EMAIL_Q3_08_2021_de_CH_HW
Germany                      EMEA_CHAN_PN_Newsletter_EMAIL_Q3_08_2021_de_DE_ALL_A
Germany                      EMEA_CHAN_PN_Newsletter_EMAIL_Q3_08_2021_de_DE_ALL_B
Switzerland                  EMEA_CHAN_PN_Newsletter_EMAIL_Q3_08_2021_fr_CH_BSD
Switzerland                  EMEA_CHAN_PN_Newsletter_EMAIL_Q3_08_2021_fr_CH_HW

Country is a custom column that is being created based on Email Name. The query is like "If it find the term "_AT_" the country will be Austria, If it find the term "_CH_" the country will be Switzerland, If it find the term "_DE_" the country will be Germany, and so on. "

You can see that for last 4 data the query is working correctly - but for the first 4 it is not.

I am stuck in the middle of a stange situation, any suggestion?

Regards

Utsav

[ryan_mayu]

@utsavlexmark 

maybe you can try add column from examples

[Greg_Deckler]

@utsavlexmark In DAX there is no general distinction between cases so that might be the issue if you are using SEARCH for example which is case-insensitive versus FIND which is case-sensitive. Would need to know your code though and if you are even using DAX or PQ.

[utsavlexmark]

Here is the code

Country 1 =

VAR country = 'Data Sources'[Email Name]

RETURN

SWITCH( TRUE(),

SEARCH( "_NL_",country,1, 0) > 0 , "Netherland",

SEARCH( "_BE_", country,1, 0) > 0 , "Belgium",

SEARCH( "_DE_", country,1, 0) > 0 , "Germany",

SEARCH( "_AT_", country,1, 0) > 0 , "Austria",

SEARCH( "_CH_", country,1, 0) > 0 , "Switzerland",

SEARCH( "_FR_", country,1, 0) > 0 , "France",

SEARCH( "_SE_", country,1, 0) > 0 , "Sweden",

SEARCH( "_DK_", country,1, 0) > 0 , "Denmark",

SEARCH( "_FI_", country,1, 0) > 0 , "Finland",

SEARCH( "_NO_", country,1, 0) > 0 , "Norway",

SEARCH( "_GB_", country,1, 0) > 0 , "UK",

SEARCH( "_UK_", country,1, 0) > 0 , "UK"

)

[Greg_Deckler · Accepted solution]

@utsavlexmark Change your SEARCH to FIND

[utsavlexmark]

Adding to my question, if I have to pull the Country from Email Name in such way, what could be the ideal query?