Forum Discussion

utsavlexmark's avatar
utsavlexmark
Icon for Helper III rankHelper III
5 years ago
Solved

Custom Column's Query showing Discrepancy

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

Need Help
Tips and Tricks

5 Replies

  • utsavlexmark's avatar
    utsavlexmark
    Icon for Helper III rankHelper III
    5 years ago

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

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

    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's avatar
      utsavlexmark
      Icon for Helper III rankHelper III
      5 years ago
      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"
      )