Forum Discussion
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
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
5 Replies
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'
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
