topic Re: Search from the end of the string in DAX Commands and Tips

Search from the end of the string

tatevikz — Thu, 11 Jun 2020 09:01:32 GMT

Hi,

I have an issue with searching in string using DAX.

Here is an example of a string where I need to find the last direcotry:

/Usr/agerbats/myproducts/ip/v-logic_gf22nsdvlogl36hdl116a/

Could someone help me to solve this issue?

Thanks

Re: Search from the end of the string

lbendlin — Thu, 11 Jun 2020 11:36:16 GMT

This is amazingly difficult in DAX as the language has no recursivity concept (i think). Does it have to be in DAX?

Anyway, here's how I would do it.

version a: Your path before the last folder has a fixed length

var path = "/Usr/agerbats/myproducts/ip/v-logic_gf22nsdvlogl36hdl116a/"

var lastfolder = right(path,len(path)-25)

return left(lastfolder,len(lastfolder)-1)

version b: flexible length and depth

Use SUBSTITUTE() to get rid of the first n slashes one by one

var path = "/Usr/agerbats/myproducts/ip/v-logic_gf22nsdvlogl36hdl116a/"

var s1=substitute(path,"/","(1)",1)

var s2=substitute(s1,"/","(2)",1)

var s3=substitute(s2,"/","(3)",1)

var s4=substitute(s3,"/","(4)",1)

var s5=substitute(s4,"/","(5)",1)

var s6=substitute(s5,"/","(6)",1)

This would give you s6 as "(1)Usr(2)agerbats(3)myproducts(4)ip(5)v-logic_gf22nsdvlogl36hdl116a(6)"

and so on if you have deeper paths. You would have to stop when variables are the same. Ie when s5 is the same as s6 that means there are only five slashes in the path.

After that you would use FIND or SEARCH to find "(5)" and extract the folder accordingly.

Re: Search from the end of the string

Anonymous — Thu, 11 Jun 2020 11:58:48 GMT

Hi tatevikz ,

You can create a Calculated Column

Column =

var a =

SUBSTITUTE('Table'[Column1],"/","|")

Var valuetoreturn = PATHITEMREVERSE(a,2)

RETURN

valuetoreturn

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Re: Search from the end of the string

tatevikz — Fri, 12 Jun 2020 06:56:28 GMT

Hi Anonymous ,

Thanks for the answer.

It works greate!!!!

Regards,

Tatevik