Forum Discussion
DAX SEARCH Function using a variable as start position.
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 ,
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,