Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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"
)
Solved! Go to Solution.
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
Nice!!
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
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |