Conditional column using text string

[nalinda116]

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!

[v-juanli-msft]

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.

[amitchandak]

@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://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-num...

https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values

[nalinda116]

Thanks for the reply!

But in involves "text" reading. I updated the original post with an example.

[v-juanli-msft]

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.