Forum Discussion

Kumshan450's avatar
Kumshan450
Helper III
8 years ago
Solved

IF functions to find text within a string

I want to find text within a string, want to return two values. 

 

ASSET_DESCProduct
PASSENGER CARRIERCV =If a text contains "Carr" then CV otherwise MC
PLATINAMC
PULSARMC

 

I entered the below formula but it's not working. 

 

Product = if(iserror(search("CAR",'Allocation dump required MP state'[ASSET_DESC])),"CV","MC")

 

Also, I wanted to add a column based on three column values. 

 

PRODUCTLIVE_EXPBOM BUCKETCLASSIFICATION
MC/CVEXPirrespective of bucketNPA
MC/CVLIV6+NPA
MC  LIVo or 10 to 1 bucket
MCLIV2  or 3 or 4 or 5 or 62 to 6 bucket
CVLIV0X bucket
CVLIV1 or 2  or 3 or 4 or 5 or 61 to 6 bucket

 

 

Need Help
  • MariaP's avatar
    MariaP
    8 years ago

    Hi Kumshan450

    Based on your data I created 2 tables and added the following 2 columns:

     

    Create a column for asset type:

    Asset_Type = 

    var myResult = IFERROR(SEARCH("CAR",[ASSET_DESC]),-1)

    RETURN

     IF (myResult = -1,  "MC", "CV")

    Create this if statement for your other bins:

     

    Classification2 = IF

    ( 'PRODUCT'[Product] = "MC/CV" && 'PRODUCT'[Live_Exp] = "EXP", "NPA",

    IF ('PRODUCT'[Product] = "MC/CV" && 'PRODUCT'[Live_Exp] = "LIV", "NPA",

        IF ('PRODUCT'[Product] = "MC" && 'PRODUCT'[Live_Exp] = "LIV" && 'PRODUCT'[Bom_Bucket_Real] IN {0,1}   , "0 to 1 bucket",  

        IF ('PRODUCT'[Product] = "MC" && 'PRODUCT'[Live_Exp] = "LIV" && 'PRODUCT'[Bom_Bucket_Real] IN {2,3,4,5,6}   , "2 to 6 bucket",   

        IF ('PRODUCT'[Product] = "CV" && 'PRODUCT'[Live_Exp] = "LIV" && 'PRODUCT'[Bom_Bucket_Real] IN {1,2,3,4,5,6}   , "1 to 6 bucket",

        IF ('PRODUCT'[Product] = "CV" && 'PRODUCT'[Live_Exp] = "LIV" && 'PRODUCT'[Bom_Bucket_Real] = 0    , "X bucket",

        IF ('PRODUCT'[Product] = "CV" && 'PRODUCT'[Live_Exp] = "LIV" && 'PRODUCT'[Bom_Bucket_Real] IN {1,2,3,4,5,6}  , "1 to 6 bucket",

     BLANK() ) ) )))))

    Results:

     

    POWER BI FILE INCASE NEEDED:

    SampleFile

     

    Hope this is interpreted as you require.

     

1 Reply

  • MariaP's avatar
    MariaP
    Solution Supplier
    8 years ago

    Hi Kumshan450

    Based on your data I created 2 tables and added the following 2 columns:

     

    Create a column for asset type:

    Asset_Type = 

    var myResult = IFERROR(SEARCH("CAR",[ASSET_DESC]),-1)

    RETURN

     IF (myResult = -1,  "MC", "CV")

    Create this if statement for your other bins:

     

    Classification2 = IF

    ( 'PRODUCT'[Product] = "MC/CV" && 'PRODUCT'[Live_Exp] = "EXP", "NPA",

    IF ('PRODUCT'[Product] = "MC/CV" && 'PRODUCT'[Live_Exp] = "LIV", "NPA",

        IF ('PRODUCT'[Product] = "MC" && 'PRODUCT'[Live_Exp] = "LIV" && 'PRODUCT'[Bom_Bucket_Real] IN {0,1}   , "0 to 1 bucket",  

        IF ('PRODUCT'[Product] = "MC" && 'PRODUCT'[Live_Exp] = "LIV" && 'PRODUCT'[Bom_Bucket_Real] IN {2,3,4,5,6}   , "2 to 6 bucket",   

        IF ('PRODUCT'[Product] = "CV" && 'PRODUCT'[Live_Exp] = "LIV" && 'PRODUCT'[Bom_Bucket_Real] IN {1,2,3,4,5,6}   , "1 to 6 bucket",

        IF ('PRODUCT'[Product] = "CV" && 'PRODUCT'[Live_Exp] = "LIV" && 'PRODUCT'[Bom_Bucket_Real] = 0    , "X bucket",

        IF ('PRODUCT'[Product] = "CV" && 'PRODUCT'[Live_Exp] = "LIV" && 'PRODUCT'[Bom_Bucket_Real] IN {1,2,3,4,5,6}  , "1 to 6 bucket",

     BLANK() ) ) )))))

    Results:

     

    POWER BI FILE INCASE NEEDED:

    SampleFile

     

    Hope this is interpreted as you require.