Forum Discussion

LUCASM's avatar
LUCASM
Helper IV
7 years ago
Solved

CASE Statement with Like

I need to replicate this SQL case statement in Power BI somehow.

 

 

CASE

      WHEN [PC Name] like '%ZENBUS%' THEN 'BUSINESS'

      WHEN [PC Name] like '%ZENWEB%' THEN 'ONLINE'

      WHEN [PC Name] like '%ZENUSE%' THEN 'STORE'

      ELSE [PC Name]

END AS Business

 

 

My main issue is when I try to write like below as a New Measure my field [PC Name] is not recognised....

Business = if "ZENBUS", '[PC Name] then "Business"
so I'm stumped right from the start.

 

 

 

Need Help
  • parry2k's avatar
    parry2k
    7 years ago

    LUCASM try this

     

    Type = 
SWITCH( TRUE(),
SEARCH( "AZN ZENBUS", Table2[col],,0) <> 0 , "Business",
SEARCH( "AZN ZENWEB", Table2[col],,0) <> 0 , "Online",
"Store"
)

8 Replies

  • Omega's avatar
    Omega
    Impactful Individual
    7 years ago

    Create a column instead of a measure and try the below formula: 

     

    Column = Switch (True (), [PC Name] = "%ZENBUS%", "BUSINESS",
      [PC Name] = "%ZENWEB%", "ONLINE",
      [PC Name] = "%ZENUSE%","STORE",
      [PC Name])
    • LUCASM's avatar
      LUCASM
      Helper IV
      7 years ago

      Hi khder312

      I'm not sure that works. All my results default to [PC Name].

      It maybe that this is because it is not a like but an equals even if we use "%xxxxx%"

       

      I do however agree with the column part of your solution which I just found.

  • themistoklis's avatar
    themistoklis
    Community Champion
    7 years ago

    LUCASM

     

    If you want to write it with if statements it should be like this:

    Business = IF (
    SEARCH ( "*ZENBUS*", Table1[PC Name],, 0 ) = 0,
    IF ( SEARCH ( "*AZENWEB*", Table1[PC Name],, 0 ) = 0, 
	IF ( SEARCH ( "*ZENUSE*", Table1[PC Name],, 0 ) = 0, [PC Name], "STORE" ),
    "ONLINE"),
	"BUSINESS"
)

     

     

    • LUCASM's avatar
      LUCASM
      Helper IV
      7 years ago

      Hi themistoklis

       

      This solution has a similar problem to that of khader312

      every row is the same rather than running through the IFs

      could this be because every row starts with ZENUS and either has ZENBUS or ZENWEB also in the text

      examples of full fields

      ZENUSE AZN ZENBUS IT Needs to become "Business"

      ZENUSE AZN ZENWEB IT Needs to become "Online"

      ZENUSE AZN ZEN IT Needs to become "Store"

      ZENBUS EU SARL Needs to become "Store"

       

      Logic: Check the first two otherwise "Store"

       

      Apologise if this was not clear first time around

      • parry2k's avatar
        parry2k
        Super User
        7 years ago

        LUCASM try this

         

        Type = 
SWITCH( TRUE(),
SEARCH( "AZN ZENBUS", Table2[col],,0) <> 0 , "Business",
SEARCH( "AZN ZENWEB", Table2[col],,0) <> 0 , "Online",
"Store"
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    I can't stand Power BI and the Power BI community.