PATHITEM function to extract string from the URL

[harsha449]

Hi,

I'm trying to extract string from the URL using PATHITEM function, it is working fine for DAX when the data is in excel.

Now I'm connect to direct query, i need to use DAX formula compatibilty in DirectQuery mode.

https://docs.microsoft.com/en-us/sql/analysis-services/tabular-models/dax-formula-compatibility-in-d...

Example:

URL: /country/english/pages/careers/article

I want to extract "careers" in new column.

DAX formula is working fine: PATHITEM(SUBSTITUTE(COLUMN,"/","|"),5)

Could you please help me with the function to do the same for direct query

Thanks

[v-chuncz-msft]

@harsha449,

To ensure that queries sent to the underlying data source have acceptable performance, limitations are imposed on measures by default. Advanced users can choose to bypass this limitation by selecting File > Options and settings > Options and then DirectQuery, then selecting the option Allow unrestricted measures in DirectQuery mode.

https://docs.microsoft.com/en-us/power-bi/desktop-use-directquery#limitations-of-directquery

[harsha449]

i have turned on that option, it is still not working.

I have tried MID and SEARCH functions, I'm getting error message "Search" and "Find" are not allowed as part of calculated column DAX.

As per the link below: we need to use 4 argumnet instead of 2 argument.

http://community.powerbi.com/t5/Desktop/FIND-SEARCH-in-direct-query-mode/td-p/198993

I'm trying that option but still not luck.

[v-chuncz-msft]

@harsha449,

The nested FIND Function seems to work for me.

[harsha449]

Thanks Sam for looking into it.

=MID(H5,SEARCH("pages/",H5,1)+5,(SEARCH("/",H5,SEARCH("pages/",H5,1)+6)-(SEARCH("pages/",H5,1)+5)))

Could you please help in editing it.

to get "Careers" in new column.

Regards,

Sri

[v-chuncz-msft]

@harsha449,

Specify the fourth parameter NotFoundValue as well.

[harsha449]

When i try to use 4th parameter, I'm getting a error message saying i have used many arguments.

Thanks,

Sri

[harsha449]

Hi Sam,

Could you please help me with this.

Thanks,

Sri