topic Re: IF function _ I need to find a 6 digit number in a textcolumn in DAX Commands and Tips

IF function _ I need to find a 6 digit number in a textcolumn

gjelstrup — Mon, 08 May 2023 07:49:25 GMT

Dear Community,

I hope you can help me. I need to create a calculated column to identyfy the lines with a 6 digit number in the text.

Is there a function to identify the notifictn(unic number) that has a 6 digit number in the material text(placed at different positions and with different 6 digit numbers)?

Re: IF function _ I need to find a 6 digit number in a textcolumn

tamerj1 — Mon, 08 May 2023 09:12:20 GMT

Hi gjelstrup
Please test the following. I did not have the chance to test from my side.

6 digit number = VAR String = 'Table'[Material Description] VAR Items = SUBSTITUTE ( String, " ", "|" ) VAR Length1 = COALESCE ( PATHLENGTH ( Items ), 1 ) VAR T1 = GENERATESERIES ( 1, Length1, 1 ) VAR T2 = SELECTCOLUMNS ( T1, "@Item1", PATHITEM ( Items, [Value] ) ) VAR T3 = FILTER ( T2, VAR Item1 = COALESCE ( [@Item1], 1 ) VAR Length2 = LEN ( Item1 ) VAR T4 = GENERATESERIES ( 1, Length2, 1 ) VAR T5 = SELECTCOLUMNS ( T4, "@Item2", MID ( Item1, 1, [Value] ) ) VAR T6 = GENERATESERIES ( 0, 10, 1 ) RETURN LEN ( Item1 ) = 6 && ISEMPTY ( EXCEPT ( T5, T6 ) ) ) RETURN MAXX ( T3, [@Item] )

Re: IF function _ I need to find a 6 digit number in a textcolumn

gjelstrup — Mon, 08 May 2023 11:11:55 GMT

Hi tamerj1 I really apprecialte your help. Thank you.

I cannot make it Work. I think that I need to change what is marked by ned in below scrren dump, however, I am not certain what I need to change it to.

Re: IF function _ I need to find a 6 digit number in a textcolumn

tamerj1 — Mon, 08 May 2023 11:17:26 GMT

gjelstrup

There is one extra closing square bracket. Please delete

Re: IF function _ I need to find a 6 digit number in a textcolumn

JamesH2405 — Tue, 09 May 2023 10:20:30 GMT

Sorry, im really new to this BI stuff, where are you putting this code, in a measure or a new column?

Re: IF function _ I need to find a 6 digit number in a textcolumn

tamerj1 — Tue, 09 May 2023 11:23:38 GMT

JamesH2405

This is a calculated column

Re: IF function _ I need to find a 6 digit number in a textcolumn

gjelstrup — Thu, 11 May 2023 08:32:16 GMT

Hi tamerj1

Thank you very much for your feedback. Now it only gives me one error :

Re: IF function _ I need to find a 6 digit number in a textcolumn

tamerj1 — Thu, 11 May 2023 08:57:24 GMT

gjelstrup

Yes, this is a typo mistake, the price paid for not testing. It should be [@Item2]

Re: IF function _ I need to find a 6 digit number in a textcolumn

gjelstrup — Fri, 12 May 2023 07:14:21 GMT

Dear tamerj1 - It is unfortunately still not Working. I appreciate very much your support. I have put the error message below in a screendump and the coding below.

Thank you for helping me out !

6 digit number =

VAR String = 'QM11'[Material description]

VAR Items =

SUBSTITUTE ( String, " ", "|" )

VAR Length1 =

COALESCE ( PATHLENGTH ( Items ), 1 )

VAR T1 =

GENERATESERIES ( 1, Length1, 1 )

VAR T2 =

SELECTCOLUMNS ( T1, "@Item1", PATHITEM ( Items, [Value] ) )

VAR T3 =

FILTER (

T2,

VAR Item1 =

COALESCE ( [@Item1], 1 )

VAR Length2 =

LEN ( Item1 )

VAR T4 =

GENERATESERIES ( 1, Length2, 1 )

VAR T5 =

SELECTCOLUMNS ( T4, "@Item2", MID ( Item1, 1, [Value] ) )

VAR T6 =

GENERATESERIES ( 0, 10, 1 )

RETURN

LEN ( Item1 ) = 6

&& ISEMPTY ( EXCEPT ( T5, T6 ) )

)

RETURN

MAXX ( T3, [@Item2] )

Re: IF function _ I need to find a 6 digit number in a textcolumn

tamerj1 — Fri, 12 May 2023 08:02:10 GMT

gjelstrup

Apologies again. It should be [@Item1]

6 digit number =

VAR String = 'QM11'[Material description]

VAR Items =

SUBSTITUTE ( String, " ", "|" )

VAR Length1 =

COALESCE ( PATHLENGTH ( Items ), 1 )

VAR T1 =

GENERATESERIES ( 1, Length1, 1 )

VAR T2 =

SELECTCOLUMNS ( T1, "@Item1", PATHITEM ( Items, [Value] ) )

VAR T3 =

FILTER (

T2,

VAR Item1 =

COALESCE ( [@Item1], 1 )

VAR Length2 =

LEN ( Item1 )

VAR T4 =

GENERATESERIES ( 1, Length2, 1 )

VAR T5 =

SELECTCOLUMNS ( T4, "@Item2", MID ( Item1, 1, [Value] ) )

VAR T6 =

GENERATESERIES ( 0, 10, 1 )

RETURN

LEN ( Item1 ) = 6

&& ISEMPTY ( EXCEPT ( T5, T6 ) )

)

RETURN

MAXX ( T3, [@Item1] )