DAX SEARCH Function using a variable as start position.

[HughLa]

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

[v-yalanwu-msft · Accepted solution]

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.

[HughLa]

Hi @v-yalanwu-msft 

Thank you very much. 

Hugh

[rsbin]

@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,