Split a string into a list in DAX

[newpbiuser01]

Hello,

I am trying to split a string to create a list so I can flag all items that are part of my string and are found in the data table. Unfortunately I cannot do this in Power Query, so I'm trying to do this in a DAX measure. 

As as example, my string [List to Check] = "Strawberry, Grapes". 

I tried the following measure to split it into a list:

Measure =

VAR mymeasure =

    SUBSTITUTE ( [List to Check], ",", "|" )

VAR Mylen =

    PATHLENGTH ( mymeasure )

VAR mytable =

    ADDCOLUMNS (

        GENERATESERIES ( 1, mylen ),

        "mylist", VALUE ( PATHITEM ( mymeasure, [Value],TEXT ) )

    )

VAR mylist =

    SELECTCOLUMNS ( mytable, "list", [mylist] )

RETURN

   IF(MAX('Table - List'[Item]) IN mylist, "Yes", "No")

Data Table:

Item

Apple

Pear

Orange

Grapes

Watermelon

Strawberry

I get the following error: 

I can't figure out what I'm doing wrong! My list is not numerical, I have clearly specified that the value is text. Would anyone know what I'm doing wrong? I'd really appreciate any help!

Thank you!

[parry2k · Accepted solution]

@newpbiuser01 why you are using VALUE function here :

VALUE ( PATHITEM ( mymeasure, [Value],TEXT ) )

[newpbiuser01]

Hi @parry2k, 

I'm using the Value function to get the actual value of the string. You did point out correctly though! Once I remove the Value function, the measure works perfectly! Thank you so much. I couldn't understand why it wouldn't work!