Forum Discussion

pbiforum123's avatar
pbiforum123
Post Patron
3 years ago
Solved

match statement help

How to check whether "AWA" exists in "AWA O-2VL9K". If it exists then return "AWA" else return the value as it is.   Please assume both of this is from a differen column.    
Need Help
  • Ahmedx's avatar
    Ahmedx
    3 years ago

    and try this

pbiforum123's avatar
pbiforum123
Post Patron
3 years ago
Ahmedx  how to handle this scenario when the values in the table is as below. Because everytime it will return CON only. Please suggest
 

 

 
IF(CONTAINSSTRING('Table-2'[Campaign Objective Code], "CON"), "CON",
IF(CONTAINSSTRING('Table-2'[Campaign Objective Code], "CONSI"), "CONSI",
 
AWA Check =
IF(CONTAINSSTRING('Table-2'[Campaign Objective Code], "APPIN"), "APPIN",
IF(CONTAINSSTRING('Table-2'[Campaign Objective Code], "APPRE"), "APPRE",
IF(CONTAINSSTRING('Table-2'[Campaign Objective Code], "AWA"), "AWA",
IF(CONTAINSSTRING('Table-2'[Campaign Objective Code], "CALIB"), "CALIB",
IF(CONTAINSSTRING('Table-2'[Campaign Objective Code], "CATSA"), "CATSA",
IF(CONTAINSSTRING('Table-2'[Campaign Objective Code], "CON"), "CON",
IF(CONTAINSSTRING('Table-2'[Campaign Objective Code], "CONSI"), "CONSI",
IF(CONTAINSSTRING('Table-2'[Campaign Objective Code], "ENG"), "ENG",
IF(CONTAINSSTRING('Table-2'[Campaign Objective Code], "EVENT"), "EVENT",
IF(CONTAINSSTRING('Table-2'[Campaign Objective Code], "FOLLOW"), "FOLLOW",
IF(CONTAINSSTRING('Table-2'[Campaign Objective Code], "FOLLW"), "FOLLW",
IF(CONTAINSSTRING('Table-2'[Campaign Objective Code], "INSTR"), "INSTR",
IF(CONTAINSSTRING('Table-2'[Campaign Objective Code], "LDS"), "LDS",
IF(CONTAINSSTRING('Table-2'[Campaign Objective Code], "MSG"), "MSG",
IF(CONTAINSSTRING('Table-2'[Campaign Objective Code], "MULTI"), "MULTI",
IF(CONTAINSSTRING('Table-2'[Campaign Objective Code], "PGLK"), "PGLK",
IF(CONTAINSSTRING('Table-2'[Campaign Objective Code], "PURC"), "PURC",
IF(CONTAINSSTRING('Table-2'[Campaign Objective Code], "REACH"), "REACH",
IF(CONTAINSSTRING('Table-2'[Campaign Objective Code], "REATRA"), "REATRA",
IF(CONTAINSSTRING('Table-2'[Campaign Objective Code], "SALE"), "SALE",
IF(CONTAINSSTRING('Table-2'[Campaign Objective Code], "STORE"), "STORE",
IF(CONTAINSSTRING('Table-2'[Campaign Objective Code], "TRAFF"), "TRAFF",
IF(CONTAINSSTRING('Table-2'[Campaign Objective Code], "VID"), "VID",
'Table-2'[Campaign Objective Code])))))))))))))))))))))))
 
 
 
  • pbiforum123's avatar
    pbiforum123
    Post Patron
    3 years ago

    Ahmedx Thanks a lot for this. I will check this.

     

    Any idea how we can automate this code. In the sense currently I checking for one by one dim_campaign_pca[campaignObjective] by hardcoding the value, so if any new value is added in the dim_campaign_pca[campaignObjective] then we have to update the code manually everytime, so to avoid this manual activity we need to automate it. We need you help for this.

     

    campaignObjectiveKey =
    IF(CONTAINSSTRING(dim_campaign_pca[campaignObjective], "APPIN"), "APPIN",
    IF(CONTAINSSTRING(dim_campaign_pca[campaignObjective], "APPRE"), "APPRE",
    IF(CONTAINSSTRING(dim_campaign_pca[campaignObjective], "AWA"), "AWA",
    IF(CONTAINSSTRING(dim_campaign_pca[campaignObjective], "CALIB"), "CALIB",
    IF(CONTAINSSTRING(dim_campaign_pca[campaignObjective], "CATSA"), "CATSA",
    IF(CONTAINSSTRING(dim_campaign_pca[campaignObjective], "CONSI"), "CONSI",
    IF(CONTAINSSTRING(dim_campaign_pca[campaignObjective], "CON"), "CON",
    IF(CONTAINSSTRING(dim_campaign_pca[campaignObjective], "ENG"), "ENG",
    IF(CONTAINSSTRING(dim_campaign_pca[campaignObjective], "EVENT"), "EVENT",
    IF(CONTAINSSTRING(dim_campaign_pca[campaignObjective], "FOLLOW"), "FOLLOW",
    IF(CONTAINSSTRING(dim_campaign_pca[campaignObjective], "FOLLW"), "FOLLW",
    IF(CONTAINSSTRING(dim_campaign_pca[campaignObjective], "INSTR"), "INSTR",
    IF(CONTAINSSTRING(dim_campaign_pca[campaignObjective], "LDS"), "LDS",
    IF(CONTAINSSTRING(dim_campaign_pca[campaignObjective], "MSG"), "MSG",
    IF(CONTAINSSTRING(dim_campaign_pca[campaignObjective], "MULTI"), "MULTI",
    IF(CONTAINSSTRING(dim_campaign_pca[campaignObjective], "PGLK"), "PGLK",
    IF(CONTAINSSTRING(dim_campaign_pca[campaignObjective], "PURC"), "PURC",
    IF(CONTAINSSTRING(dim_campaign_pca[campaignObjective], "REACH"), "REACH",
    IF(CONTAINSSTRING(dim_campaign_pca[campaignObjective], "REATRA"), "REATRA",
    IF(CONTAINSSTRING(dim_campaign_pca[campaignObjective], "SALE"), "SALE",
    IF(CONTAINSSTRING(dim_campaign_pca[campaignObjective], "STORE"), "STORE",
    IF(CONTAINSSTRING(dim_campaign_pca[campaignObjective], "TRAFF"), "TRAFF",
    IF(CONTAINSSTRING(dim_campaign_pca[campaignObjective], "VID"), "VID",
    dim_campaign_pca[campaignObjective])))))))))))))))))))))))