topic Re: Trying to search a bracketed list in DAX Commands and Tips

Trying to search a bracketed list

Thomas_MedOne — Thu, 15 Jun 2023 19:38:11 GMT

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.

Re: Trying to search a bracketed list

tamerj1 — Thu, 15 Jun 2023 20:14:02 GMT

Hi Thomas_MedOne

If just 9 as you have mentioned, then please try

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

Re: Trying to search a bracketed list

Thomas_MedOne — Thu, 15 Jun 2023 20:16:58 GMT

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]

Re: Trying to search a bracketed list

tamerj1 — Thu, 15 Jun 2023 20:46:51 GMT

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"
)

Re: Trying to search a bracketed list

tamerj1 — Fri, 16 Jun 2023 06:19:36 GMT

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" )

Re: Trying to search a bracketed list

Thomas_MedOne — Fri, 16 Jun 2023 12:12:53 GMT

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