How to fill BRAND_5 = Null with PRO_CLASS first character ?

[Paulyeo11]

Hi All

Can some one share with me how to write this expression ?

My PBI file :-

https://www.dropbox.com/s/4ysqturttdwcdf4/PB_TDS_V080%20RECODE%20BRAND%20TO%20PROJECT.pbix?dl=0

[PhilipTreacy · Accepted solution]

Hi @Paulyeo11 

Try this SWITCH statement

Column 2 = SWITCH(

                    TRUE(),

                    LEN([BRAND_5]) = 0 && LEN([PRO_CLASS]) = 0 , "No Brand",
                    
                    LEN([BRAND_5]) = 0 && SEARCH(" ", SALES[PRO_CLASS], 1, BLANK()) > 0 , LEFT([PRO_CLASS], SEARCH(" ", SALES[PRO_CLASS], 1, BLANK())),

                    LEN([BRAND_5]) = 0 && LEN([PRO_CLASS]) > 0 , [PRO_CLASS],

                    [BRAND_5]

)

Phil

[Paulyeo11]

Hi Phil

Can you pls help me modify the script , so that it only display 3 Core Brand ?

[Paulyeo11]

Hi Phil

Thank you very much for your help. it work wonderfull.

[PhilipTreacy]

Hi @Paulyeo11 

This will display any single word like MSA from the PRO_CLASS column

Column = IF(LEN([BRAND_5]) = 0 , IF( SEARCH(" ", SALES[PRO_CLASS], 1, BLANK()) = 0 , [PRO_CLASS] , LEFT([PRO_CLASS], SEARCH(" ", SALES[PRO_CLASS], 1, BLANK()))) , [BRAND_5] )

Phil 

[Paulyeo11]

Hi Phil

Many thank it work , pls help me make it display No Brand when PRO_CLASS is empty.

[PhilipTreacy]

Hi @Paulyeo11 

This works,

Column = IF(LEN([BRAND_5]) = 0 , LEFT([PRO_CLASS], SEARCH(" ", SALES[PRO_CLASS], 1, BLANK())), [BRAND_5] )

There's a 'feature' with SEARCH (and FIND) that you have to specify the optional 3rd and 4th parameters or it throws an error in certain cases.

Phil

[Paulyeo11]

Hi Phil

Yes it work , But when PRO_CLASS only have MSA , may i know how to pick up this word ?

[PhilipTreacy]

Hi @Paulyeo11 

This should work to return the first word from the PRO_CLASS column.  Create a new column and enter this formula

Column = IF(ISBLANK([BRAND_5]) , LEFT([PRO_CLASS], SEARCH(" " , [PRO_CLASS])), [BRAND_5] )

Phil

[Paulyeo11]

Hi Phil, Still same as Amit result :-

[amitchandak]

@Paulyeo11 , Try new column

if(isblank([brand_5]) ,[Pro_class],[brand_5] )

[Paulyeo11]

Hi Amit

I have try but when No Brand , it does not take PRO_CLASS field first charater.