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

[gjelstrup]

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

[tamerj1]

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

[gjelstrup]

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. 

[tamerj1]

@gjelstrup 

There is one extra closing square bracket. Please delete

[gjelstrup]

Hi @tamerj1 

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

[tamerj1]

@gjelstrup 

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

[gjelstrup]

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

[tamerj1]

@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] )

[JamesH2405]

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

[tamerj1]

@JamesH2405 

This is a calculated column