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
Flammie
Helper I
Helper I

Is there a way to use SWITCH with CONTAINS?

Hello,

I have a column with "PartNumber" which are long and have letters and number. Each part of the "Part Number" means something different. So we will use Parts # "123ABC" and "897BAG". I need to make a SWITCH statement that says this:

 

SWITCH (Page1[ItemNumber], if the part number contains "3AB"  then say "GroupA", if the part number contains "BAG" then say "GroupB", else say "XXX")

 

OUTCOME:

Part "123ABC" = GroupA

Part "897BAG" = GroupB

Part "123GHEM" = "XXX"

1 ACCEPTED SOLUTION
v-yuta-msft
Community Support
Community Support

@Flammie ,

 

Create a calculate column using DAX below:

Group = 
SWITCH (
    TRUE (),
    ISERROR ( FIND ( "3AB", 'Table'[PartNumber] ) ) <> TRUE (), "GroupA",
    ISERROR ( FIND ( "BAG", 'Table'[PartNumber] ) ) <> TRUE (), "GroupB",
    ISERROR ( FIND ( "3AB", 'Table'[PartNumber] ) ) = TRUE ()
        && ISERROR ( FIND ( "BAG", 'Table'[PartNumber] ) ) = TRUE (), "XXX"
)

Capture.PNG 

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yuta-msft
Community Support
Community Support

@Flammie ,

 

Create a calculate column using DAX below:

Group = 
SWITCH (
    TRUE (),
    ISERROR ( FIND ( "3AB", 'Table'[PartNumber] ) ) <> TRUE (), "GroupA",
    ISERROR ( FIND ( "BAG", 'Table'[PartNumber] ) ) <> TRUE (), "GroupB",
    ISERROR ( FIND ( "3AB", 'Table'[PartNumber] ) ) = TRUE ()
        && ISERROR ( FIND ( "BAG", 'Table'[PartNumber] ) ) = TRUE (), "XXX"
)

Capture.PNG 

 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

@Flammie - You can use the FIND function.

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.