Multi-IF with keyword parsing

[JimmyKe]

Hi all,

    Now I have one pactice with multi IF with following condition, have any one can help me how to solve it.  Thanks

Fruits	Valueable	Results
APPLE	MER	PAK
ORANGE	TCaccTC	TSS
APPLE	TCacc	PAK
ORANGE	AccMER	TSS
ORANGE	MERACC	TSS
ORANGE	MER	LINKED
ORANGE	ACC	TSS

   As above table, if first column "Fruits" encounter "APPLE" then in the result column need show "PAK" and at the meantime at second column "Valueable" has keyword of "acc" or "ACC" then in the "result" column need to show "TSS" otherwise need to show "LINKED".  I have no idea how to deal with this multi-column with keyword.  Please help me on this.  Thanks!

[v-frfei-msft]

Hi @JimmyKe ,

Results Column = 
  SWITCH(TRUE(),
    [Fruits] = "APPLE","PAK",
    SEARCH("acc",[Valueable],,0)<>BLANK(),"TSS",
    "LINKED"
   )

Pbix as attached.

[Ashish_Mathur · Accepted solution]

Hi,

Try this calculated column formula

Results = IF([Fruits] = "APPLE","PAK",IF(SEARCH("acc",[Fruits],,0)>0,"TSS","LINKED"))

Hope this helps.

[amitchandak]

@JimmyKe 

Use Switch and upper and lower function to deal with case

Switch( TRUE(),
Table[fruits]="APPLE", "PAK",
search("ACC",upper(Table[Valueable]),,0) >0 ,"TSS",
"LINKED"
)

[Greg_Deckler]

Seems like a SWITCH(TRUE()..) statement to me:

Results Column =
  SWITCH(TRUE(),
    [Fruits] = "APPLE","PAK",
    SEARCH("acc",[Fruits],,0),"TSS",
    "LINKED"
   )

It's always amazing to me how many data problems deal with fruits... There must be this huge industry ecosystem of people that deal in fruit data that I have never worked with... Especially Apples and Oranges, it's uncanny much data revolves around those two fruits...