Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Extract document number from a string using a list from another table as reference

Preferably as a DAX calculated column, I am looking for a way of extracting a document number from a string of text that exists in a column of one table whenever the starting values from a list that ...
Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    3 years ago

    Hi Anonymous ,

    Pls adjust to the below:

    Doc Number2 =
VAR Table_ =
    MAXX (
        FILTER ( Table2, SEARCH ( Table2[Column2], Table1[Column1],, 0 ) > 0 ),
        Table2[Column2]
    )
RETURN
    VAR Start_p =
        SEARCH ( Table_, Table1[Column1], 1 )
    VAR End_p =
        IFERROR (
            IFERROR (
                IFERROR (
                    SEARCH ( ".", Table1[Column1], Start_p ),
                    SEARCH ( " ", Table1[Column1], Start_p )
                ),
                SEARCH ( ",", Table1[Column1], Start_p )
            ),
            LEN ( Table1[Column1] ) + 1
        )
    VAR End_p2 =
        IFERROR (
            IFERROR (
                IFERROR (
                    SEARCH ( " ", Table1[Column1], Start_p ),
                    SEARCH ( ".", Table1[Column1], Start_p )
                ),
                SEARCH ( ",", Table1[Column1], Start_p )
            ),
            LEN ( Table1[Column1] ) + 1
        )
    VAR End_p3 =
        IF ( End_p < End_p2, End_p, End_p2 )
    RETURN
        IF (
            Table_ <> BLANK (),
            MID ( Table1[Column1], Start_p, End_p3 - Start_p ),
            ""
        )

     

    Output result:

     

    Best Regards

    Lucien

Bifinity_75's avatar
Bifinity_75
Solution Sage
3 years ago

Hi Anonymous , If you have problems with the previous formula, try this one:

C_Column = 
VAR Table_ =
    maxx(filter(Table2 , search(Table2[Column2],Table1[Column],,0)>0),Table2[Column2])
        RETURN
VAR Start_p=SEARCH(Table_,Table1[Column],1)
VAR End_p=IFERROR(
            IFERROR(
                IFERROR(
                    SEARCH(" ",Table1[Column],Start_p),
                     SEARCH(".",Table1[Column],Start_p)),
            SEARCH(",",Table1[Column],Start_p)),
          LEN(Table1[Column]))
RETURN
IF(Table_<>BLANK(),
MID(Table1[Column],Start_p,End_p-Start_p),"")

Best regards

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi, Bifinity_75 

 

First, thank you for all your help and guidance here. Sorry to bother again. I have been playing with this formula and testing different functions within, but I keep getting the same issues...

 

Below is a document I created with a sample of actual data I am working with:

 

Example Document with Data

 

Here are some of the issues I am seeing:

  1. When there is just an expected result in the search column (Table1), the last digit of the document number is being removed.
  2. When there are expected results that have extension on them separated by a "." the entire document number with the extension is still being retreived.
  3. In my full model when I run this DAX column I get the error, "The search Text provided to function 'SEARCH' could not be found in the given text." but that's something I can contonue to play with and figure out. 

Any input you can provide is greatly appreciated!

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    As an update, I got around the length issue by simply adding a "+1" to the end of the End_p VAR, which seems to have worked well, "LEN(Table1[Column1])+1)"

     
    Still trying to solve for #2 above. I thought of maybe swapping to a switch function for the searches instead but that failed.