Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JimmyKe
New Member

Multi-IF with keyword parsing

Hi all,

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

FruitsValueableResults
APPLEMERPAK
ORANGETCaccTCTSS
APPLETCaccPAK
ORANGEAccMERTSS
ORANGEMERACCTSS
ORANGEMERLINKED
ORANGEACCTSS

 

   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!

 

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

Try this calculated column formula

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

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

4 REPLIES 4
v-frfei-msft
Community Support
Community Support

Hi @JimmyKe ,

 

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

2.PNG

 

Pbix as attached.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Ashish_Mathur
Super User
Super User

Hi,

Try this calculated column formula

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

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
amitchandak
Super User
Super User

@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
Super User
Super User

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...


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors