Trying to search a bracketed list

[Thomas_MedOne]

I have this table with a field that contains data like this:

[9]

[9,19]

[19]

[4,9]

[9,17]

(and other values) Essentially it's square brackets [] with numbers in between them.

I need to find all the ones that have just 9. And I originally created the new column in Power BI like this:

COPC = If (ISERROR(SEARCH(9, Admissions[TypeIds])),"No","Yes")

But that is matching on 9 and 19 which is not good. I have searched all over and can't figure out how to re-write the dax code to find only 9's.

[tamerj1 · Accepted solution]

@Thomas_MedOne 

Please try

COPC =
VAR String =
SUBSTITUTE ( SUBSTITUTE ( Admissions[TypeIds], "[", "" ), "]", "" )
VAR Items =
SUBSTITUTE ( String, ",", "|" )
VAR Length =
COALESCE ( PATHLENGTH ( Items ), 1 )
VAR T =
GENERATESERIES ( 1, Length, 1 )
RETURN
IF (
"9" IN SELECTCOLUMNS ( T, "@Item", PATHITEM ( Items, [Value] ) ),
"Yes",
"No"
)

[tamerj1 · Accepted solution]

@Thomas_MedOne 
I've made sample file to test the proposed solution. Please refer to attached

COPC = 
VAR String =
    SUBSTITUTE ( SUBSTITUTE ( Admissions[TypeIds], "[", "" ), "]", "" )
VAR Items =
    SUBSTITUTE ( String, ",", "|" )
VAR Length =
    COALESCE ( PATHLENGTH ( Items ), 1 )
VAR T =
    GENERATESERIES ( 1, Length, 1 )
RETURN
    IF (
        "9" IN SELECTCOLUMNS ( T, "@Item", PATHITEM ( Items, [Value] ) ),
        "Yes",
        "No"
    )

[Thomas_MedOne]

That is amazing, I would have never been able to figure that out. Thank you.

[Thomas_MedOne]

Sorry I was not clear. I need all of the 9's so It also has to match like [4,9] and [9,17] and even [9,19]

[tamerj1 · Accepted solution]

@Thomas_MedOne 

Please try

COPC =
VAR String =
SUBSTITUTE ( SUBSTITUTE ( Admissions[TypeIds], "[", "" ), "]", "" )
VAR Items =
SUBSTITUTE ( String, ",", "|" )
VAR Length =
COALESCE ( PATHLENGTH ( Items ), 1 )
VAR T =
GENERATESERIES ( 1, Length, 1 )
RETURN
IF (
"9" IN SELECTCOLUMNS ( T, "@Item", PATHITEM ( Items, [Value] ) ),
"Yes",
"No"
)

[tamerj1]

Hi @Thomas_MedOne 

If just 9 as you have mentioned, then please try

COPC = IF ( Admissions[TypeIds] = "[9]", "Yes", "No" )