IF functions to find text within a string

[Kumshan450]

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

ASSET_DESC	Product
PASSENGER CARRIER	CV =If a text contains "Carr" then CV otherwise MC
PLATINA	MC
PULSAR	MC

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. 

PRODUCT	LIVE_EXP	BOM BUCKET	CLASSIFICATION
MC/CV	EXP	irrespective of bucket	NPA
MC/CV	LIV	6+	NPA
MC	LIV	o or 1	0 to 1 bucket
MC	LIV	2  or 3 or 4 or 5 or 6	2 to 6 bucket
CV	LIV	0	X bucket
CV	LIV	1 or 2  or 3 or 4 or 5 or 6	1 to 6 bucket

[MariaP · Accepted solution]

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.