Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
HughLa
Resolver IV
Resolver IV

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. 

 

HughLa_0-1668801674283.png

 

 

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

 

Regards 

Hugh

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

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:

vyalanwumsft_0-1668997443481.png


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.

View solution in original post

3 REPLIES 3
v-yalanwu-msft
Community Support
Community Support

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:

vyalanwumsft_0-1668997443481.png


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.

Hi @v-yalanwu-msft 

 

Thank you very much. 

 

Hugh

rsbin
Super User
Super User

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.