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
RvdHeijden
Post Prodigy
Post Prodigy

Switch function ?

I have a colum with different values but i want to make a new calculated column that checks the values in the other column

i believe the switch function would work here but im not sure how to use it.

 

I need a few checks in the formula.

 

He needs to check a couple of things.

 

1. IF value in column 'AP gebied naam' contains 'VB_' then "Vraagbundeling"

else

2. IF value in column 'AP gebied naam' contains '-E0' then "Nieuwbouw"

else

3. "Hoofdproject"

 

Hopefully someone can help me out here

1 ACCEPTED SOLUTION
Floriankx
Solution Sage
Solution Sage

General Switch Formula would be:

SWITCH(['AP gebied naam'];
"VB_";"Vraagbundeling"
"-E0";Niewbouw;
"Hoofdproject")

There is also a CONTAINS formula. But I have never used it before. So if you can give the formula the whole name of the phrase to check this should work.

View solution in original post

6 REPLIES 6
Floriankx
Solution Sage
Solution Sage

Just for perfection including contains:

 

=SWITCH(TRUE();
NOT(ISERROR(SEARCH("VB_";['AP gebied naam'])));"Vraagbundeling";
NOT(ISERROR(SEARCH("-E0";['AP gebied naam'])));"Nieuwbouw";
"Hoofproject")

Floriankx
Solution Sage
Solution Sage

General Switch Formula would be:

SWITCH(['AP gebied naam'];
"VB_";"Vraagbundeling"
"-E0";Niewbouw;
"Hoofdproject")

There is also a CONTAINS formula. But I have never used it before. So if you can give the formula the whole name of the phrase to check this should work.

@FloriankxCONTAINS will not help here, but your switch statement is correct and it's exactly what @RvdHeijden needs here. CONTAINS checks to see if a certain column has a certain value anywhere in the entire column. So it's not particularly useful on a row-by-row case like this one.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Will the switch function check part of thr text ? Thr VB_ is part of a value and so is the -E0 so will it still work ?

Hello,

 

it should. Please try it and let me know.

 

If more than one LogicalTest is true I think switch stops after the first true one.

 

Best regards.

No, it doesn't check substrings. The value has to literally be "VB_" or "-E0". If it's something like "VB_-E0" that won't count.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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.