cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
m4dt3o
Regular Visitor

URL Filtering with number in table name

Hi Guys,

 

i really need your help 😞

I'm not able to find a solution, i need to filter a dashboard made in pbi may22 and deployed in our report server. The dashboard is live connected to our SSAS.

The normal url filtering works like a charm for everything but the table that begin with number in the table name. In that case the filter value is just ignored (like there is not the ?filter= in the url at all).

 

a lot of the table in the SSAS model start with number, and i cannot change them (like "1 Sales" , "2 Customer", etc etc), how can i pass a filter to that tables?

 

to summirize:

?filter = Sales/Name eq 'John' ----> WORKS

?filter = 1 Sales/Name eq 'John' ---->DO NOT WORKS (ignored at all)

 

I tried a lot of encoding without success.

Do you have any suggestion?

 

thank you very much

1 ACCEPTED SOLUTION


@m4dt3o wrote:

Thank you d_gosbell, but this was the first thing i tried.


OK, but you did not mention that in your original post.

 

This does seem like a bug. I would suggest raising a support ticket for this so that an engineer can investigate this.

 

One work around that appears to work on the service is to encode the ASCII value for the leading number in the same way that the other special characters like spaces are encoded.

 

eg

 

?filter=1_x0020_table_x0020_name%2FColumn%20eq%20%27John%27

 

would become the following since 1 is ascii character 31 

 

?filter=_x0031__x0020_table_x0020_name%2FColumn%20eq%20%27John%27

 

View solution in original post

5 REPLIES 5
m4dt3o
Regular Visitor

Thank you d_gosbell, but this was the first thing i tried. IT's working for the table name/column

table_x0020_name%2FColumn%20eq%20%27John%27

 

but not for 1 table name/column

1_x0020_table_x0020_name%2FColumn%20eq%20%27John%27

 

It's not working. I tried WITHOUT success also the UNICODE of the first number. Tried several different encoding but nothing. seems that if after the "filter=" part there is a number, the filter part is just skipped.

 

I think that it can be easy replicable by using an import file (excel, csv) and giving it a name with a number as a prefix (like "1 Sales"). Then publish on a report server and try to URL filtering that table. I tested version from jan 21 to may 22 without success

 


@m4dt3o wrote:

Thank you d_gosbell, but this was the first thing i tried.


OK, but you did not mention that in your original post.

 

This does seem like a bug. I would suggest raising a support ticket for this so that an engineer can investigate this.

 

One work around that appears to work on the service is to encode the ASCII value for the leading number in the same way that the other special characters like spaces are encoded.

 

eg

 

?filter=1_x0020_table_x0020_name%2FColumn%20eq%20%27John%27

 

would become the following since 1 is ascii character 31 

 

?filter=_x0031__x0020_table_x0020_name%2FColumn%20eq%20%27John%27

 

Again Thx! my fault didn't mention it before.

 

BTW on the report server side, seems NOT working even the 

?filter=_x0031__x0020_table_x0020_name%2FColumn%20eq%20%27John%27

 

😞

 

Do you know how to raise a ticket? there is a link?

 

thank you very much again for your reply

m4dt3o
Regular Visitor

Correct myself: IT is working!!!

 

the syntax with the translated ASCII number seems to wiork. Thank you again  d_gosbell

d_gosbell
Super User
Super User

I do not think the issue is the number. It's most likely the space in the name. There are certain special characters which need special encoding and spaces are one of them (see Filter a report using query string parameters in the URL - Power BI | Microsoft Docs )

 

So instead of  ?filter = 1 Sales/Name eq 'John'

It should be ?filter = 1_x0020_Sales/Name eq 'John'

 

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors