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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
LUCASM
Helper III
Helper III

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.

 

 

 

1 ACCEPTED SOLUTION

@LUCASM try this

 

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


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

7 REPLIES 7
themistoklis
Community Champion
Community Champion

@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"
)

 

 

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

@LUCASM try this

 

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


Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Thanks parry2k

 

That is the answer I was expecting.

 

Omega
Impactful Individual
Impactful Individual

Try: 

 

Column = Switch (True (), Contains (Table Name,[PC Name], "ZENBUS"), "BUSINESS",
      Contains (Table Name,[PC Name], "ZENWEB"),"ONLINE",
      Contains (Table Name,[PC Name], "ZENUSE"),"Store",
      [PC Name])
Omega
Impactful Individual
Impactful Individual

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])

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.