Forum Discussion
Conditional column using text string
Hi,
I have a data base which include debt follow-up remarks (calls) written in unstructured manner. I need to create a new/ conditional column to classify them in to 03 categories as "effective", "not effective", "some what effective". I have identified texts that represent each classifications e.g. not effective= "cut the call", "phone not working", "number unreachable", "incorrect number".... like wise there can be 40-50 terms for each category (i can put them to a table).
Using conditional column option is one solution but has the following shortcomings;
1. It is time consuming considering number of text for each category.
2. It allows only 02 outcome/ classification, here i need 03
3. It is case sensitive and here i don't prefer it
Could anyone give a solution?
Thanks you!
4 Replies
Hi nalinda116
Or create a match table,
Then merge it in the original data,
After expand, there are some wrong rows, create a custom column,
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.nalinda116 , Not sure I got it. create a color measure using min/max of first nonblank on category column like this
Color Date = if(FIRSTNONBLANK(Table[date],TODAY()) <today(),"lightgreen","red") if(FIRSTNONBLANK(Table[Value],"true") "true","green","red") Color sales = if(AVERAGE(Sales[Sales Amount])<170,"green","red") Color Year = if(FIRSTNONBLANK(Table[Year],2014) <=2016,"lightgreen",if(FIRSTNONBLANK(Table[Year],2014)>2018,"red","yellow")) Color = if(FIRSTNONBLANK(Table[Year],2014) <=2016 && AVERAGE(Sales[Sales Amount])<170 ,"lightgreen",if(FIRSTNONBLANK(Table[Year],2014)>2018,"red","yellow"))Then in conditional formatting use field option and use this measure
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values
Thanks for the reply!
But in involves "text" reading. I updated the original post with an example.
Hi nalinda116
You could use conditional column in Transform data.
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
