topic Stripping out the query string in a URL returning blank in Report Server

Stripping out the query string in a URL returning blank

L25 — Thu, 30 Jan 2025 03:50:12 GMT

Hello, I am trying to strip out everything including and after the ? from my url [page_location] to report on page usage, I am also stripping out my own website address to shorten the result.

eg in the below random website example url

https://ortc.com.au/products/logo-quarter-zip-charcoal?variant=41124690722934

all that would be left is

/products/logo-quarter-zip-charcoal

My query, which is a result partly of these forums and our chatgpt friend, is below but keeps returning blanks for every result. If anyone has any ideas I would be extremely grateful!

Page Path =

VAR Destringed_Page_Loc =

IF(

CONTAINSSTRING(Query1[page_location], "?"),

LEFT(Query1[page_location], SEARCH("?", Query1[page_location]) - 1),

Query1[page_location]

)

RETURN SUBSTITUTE(Destringed_Page_Loc, "https://www.myurl.com.au", "")

Re: Stripping out the query string in a URL returning blank

aduguid — Thu, 30 Jan 2025 06:37:58 GMT

Try this revised DAX.

Extract Before Question = VAR _search = "?" // Define the character to search for in the URL VAR _replace = "|" // Define a replacement character for the question mark VAR _url_replace = SUBSTITUTE(MAX(Query1[page_location]), _search, _replace) // Replace the "?" with "|" in the page location (URL) VAR _url = _url_replace // Assign the modified URL to a new variable VAR _position = SEARCH(_replace, _url, 1, LEN(_url)) // Find the position of the replaced character in the URL VAR _result = LEFT(_url, _position - 1) // Extract the portion of the URL before the "|" character (excluding it) VAR _debug = "URL: " & _url & " | Search Pos: " & _position // Debugging output to see the URL and position of the replacement character RETURN _result