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
ninos-shiba
Resolver I
Resolver I

How to use SWITCH with wildcard to create a column with records that may have more than one category

I have a column labeled "Problem_Description" that contains keywords of things a customer service representative may have missed when talking on the phone with a customer. I want to create a "Category" column that has the category or categories that the rep missed. I was thinking about using the SWITCH function along with SEARCH but do not know how to implement that. Also, is it possible and how could I assign more than one category based off the problem description to a single cell or is there a way to duplicate the row and have the new category change with the row?

For example, say the problem description for a row says "Rep missed the call opener and call closure". I would want the Category column based off that SWITCH and SEARCH function to label it as "Call Opener Policy, Call Closure Policy". I would then want to create a bar chart that shows the total counts of these missed policies but I'm not sure how to do that, especially if they wouldn't be in their own (duplicated) rows.

 

EDIT: I figured out how to do the SWITCH with SEARCH function using the code below. Now I just don't know how to assign more than one category to a row.

 

COACHING_CATEGORY =
SWITCH (
TRUE (),
SEARCH ( "Open", Table'[PROB_DESCR], 1, 0 ) > 1, "Policy 1",
SEARCH ( "Close", 'Table[PROB_DESCR], 1, 0 ) > 1, "Policy 2"
)

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

HI @ninos-shiba ,

>>Also, is it possible and how could I assign more than one category based off the problem description to a single cell or is there a way to duplicate the row and have the new category change with the row?

Nope, this is impossible. BTW, switch function not support to handle records who suitable with multiple cases.(it will return first one of suitable conditions)

For merge categories, you can try to use following calculated column if it suitable for your requirement:

COACHING_CATEGORY =
VAR dict =
    DATATABLE (
        "Text", STRING,
        "Policy", STRING,
        {
            { "Open", "Policy 1" },
            { "Close", "Policy 2" },
            { "xxxx", "Policy 3" },
            { "Test", "Policy 4" }
        }
    )
RETURN
    CONCATENATEX (
        FILTER ( dict, SEARCH ( [Text], 'Table'[PROB_DESCR], 1, 0 ) > 1 ),
        [Policy],
        ","
    )

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
datafan
Helper I
Helper I

Nice!!

v-shex-msft
Community Support
Community Support

HI @ninos-shiba ,

>>Also, is it possible and how could I assign more than one category based off the problem description to a single cell or is there a way to duplicate the row and have the new category change with the row?

Nope, this is impossible. BTW, switch function not support to handle records who suitable with multiple cases.(it will return first one of suitable conditions)

For merge categories, you can try to use following calculated column if it suitable for your requirement:

COACHING_CATEGORY =
VAR dict =
    DATATABLE (
        "Text", STRING,
        "Policy", STRING,
        {
            { "Open", "Policy 1" },
            { "Close", "Policy 2" },
            { "xxxx", "Policy 3" },
            { "Test", "Policy 4" }
        }
    )
RETURN
    CONCATENATEX (
        FILTER ( dict, SEARCH ( [Text], 'Table'[PROB_DESCR], 1, 0 ) > 1 ),
        [Policy],
        ","
    )

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.