PowerBI help: Formula to categorise data based on keywords

[MishiAb]

Hiya, I'm looking for some help creating a DAX formula to help with the following (example data below):

If the Classification column contains the words 'Monkey', or 'Bear' or 'Goat' or any combination of these words, I want the 'category' column to return 'Animals'.

• If the Classification column contains the words 'Pear', or 'Apple' or 'Lychee' or any combination of these words, I want the 'category' column to return 'Fruit'
• If the Classification column contains Blank or 'No classification' I want the 'category' column to return 'None'
• And if it contains a combination of words from the 'animal' and 'fruit' groups e.g 'Monkey, Pear,  I want the 'category' column to return 'Multiple'

How the data looks

Classification

Monkey

Pear

Monkey, Bear

Pear, Apple, Lychee

Goat, Bear

Goat

I have gone round and round trying to figure this out and tried ChatGPT but keep failing. Please help!

Thank you.

[MishiAb]

Thank you both much for your help. I used the solution from tamerj1 and it worked perfectly!

[ThxAlot]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s3Py06tVIrViVYKSE0sAjMgYjoKTjABkIyOgmNBQU6qjoJPZXJGaipY3D0/sQRJGYgLZiiASSewrkSQLqXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Classification = _t]),
    Fruit = {"Pear","Apple","Lychee"},
    Animal = {"Monkey","Bear","Goat"},

    Categorized = Table.AddColumn(
            Source,
            "category",
            each let
                frt = List.Contains(Fruit, [Classification], (x,y) => Text.Contains(y,x,Comparer.OrdinalIgnoreCase)),
                anm = List.Contains(Animal, [Classification], (x,y) => Text.Contains(y,x,Comparer.OrdinalIgnoreCase))
            in
                if frt and anm then "Multiple"
                    else if frt then "fruit"
                        else if anm then "animal" else "None"
    )
in
    Categorized

[tamerj1 · Accepted solution]

@MishiAb 
Please refer to attached sample file with the proposed solution

Category = 
VAR Animals = { "Monkey", "Bear", "Goat" } -- Here you need to place all the possible values of the 1st category or reference a disconneted table
VAR Fruits = { "Apple", "Pear", "Lychee" } -- Here you need to place all the possible values of the 2nd category or reference a disconneted table
VAR String = 'Table'[Classification]
VAR Items = SUBSTITUTE ( String, ", ", "|" )
VAR Length = COALESCE ( PATHLENGTH ( Items ), 1 )
VAR T1 = GENERATESERIES ( 1, Length, 1 )
VAR T2 = SELECTCOLUMNS ( T1, "@Item", PATHITEM ( Items, [Value] ) )
VAR ConditionAnimals = NOT ISEMPTY ( INTERSECT ( T2, Animals ) )
VAR ConditionAFruits = NOT ISEMPTY ( INTERSECT ( T2, Fruits ) )
RETURN
    SWITCH ( 
        TRUE ( ),
        ConditionAnimals && ConditionAFruits, "Multiple",
        ConditionAnimals, "Animals",
        ConditionAFruits, "Fruits",
        "None"
    )