Forum Discussion

HughLa's avatar
HughLa
Resolver IV
3 years ago
Solved

DAX SEARCH Function using a variable as start position.

Hi

 

I would like to know if there is a work around or if it is possible at all to use a variable in the SEARCH function.

 

I theoretically want to find the second occurance of a value in a string. 

First, I set a variable to the position of the first occurance, if there is none I set it to 0.

Then, I want to return the next occurance by starting at the position that is set in the variable. But the Search function does not allow the variable. 

 

 

 

If there is a different way to do this please share.

 

Regards 

Hugh

Need Help
Tips and Tricks
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    3 years ago

    Hi, HughLa ;

    Since the position of search() cannot start from 0, because the result after your first search has 0; So the second lookup needs to start at +1. You can refer to the following formula:

    Column = 
 var _posi=SEARCH("-",[NAME],1,0)
 return SEARCH("-",[NAME],_posi+1,_posi)

    The final show:


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • rsbin's avatar
    rsbin
    Community Champion
    3 years ago

    HughLa ,

    You are on the right track with the Search.  Here is a solution using multiple columns.

    1.   Length = LEN([ProductDescription])  // Find the length of your [ProductDescription]

    2.    Search 1st = SEARCH("-", [ProductDescription],,0 )  // Finds first occurrence of "-"

    3.   MID = MID( [Name], [Search 1st]+1, [Length] - [Search 1st]+1 )  //  Extract the remainder of the String after the first occurrence

    4.  Search 2nd = SEARCH("-", [MID],,0 )  // Apply the Search function again on the Remaining string.

    NAME Length Search 1st MID Search 2nd
    1110009988 10 0 1110009988 0
    88888-GGGG-DDDD- 16 6 GGGG-DDDD- 5
    AGL-0001 8 4 0001 0
    2344778678 10 0 2344778678 0
    5278-837HJ- 11 5 837HJ- 6
    DHL-88747474 12 4 88747474 0
    BIG-XX87655 11 4 XX87655 0
    JIB-129499- 11 4 129499- 7
    37HHU7733 9 0 37HHU7733 0

     

    With additional effort, you can combine all of this in one column.  I wanted to clearly demonstrate the logic behind my approach.

    Hope you can put this to good use.

    Regards,

  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    Community Support
    3 years ago

    Hi, HughLa ;

    Since the position of search() cannot start from 0, because the result after your first search has 0; So the second lookup needs to start at +1. You can refer to the following formula:

    Column = 
 var _posi=SEARCH("-",[NAME],1,0)
 return SEARCH("-",[NAME],_posi+1,_posi)

    The final show:


    Best Regards,
    Community Support Team _ Yalan Wu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.