Forum Discussion
Filter a directQuery with parameter and apply parameter change within URL call (PBIRS sept2021)
Hello all,
I try to accomplish a very simple task: have a pbi installed on my PBIRS on premise that is able to "direct query" with filter parameter.
I have a table USER and want to apply the filter when the query run.
All works fine in PowerBI Desktop (optimized for PBIRS Sept2021), when I change the parametre value in "manage parameter" the query is correctly filtered.
When I publish the pbi on PBIRS and call it with a filter parameter : xxx/powerbi/DirectQueryParam?USER_PARAM=3
The pbi always return the row filtered in PowerBi Desktop (USER 1), I'am unable to override the parameter whit the URL call.
DirectQueryParam is the name of the pbi
USER_PARAM is the parameter
Here the query:
let
Source = Sql.Database("server", "database", [Query="SELECT "&Number.ToText(USER_PARAM)&" PARAM, ID_USER, ID_PROFIL FROM USER where ID_USER ="&Number.ToText(USER_PARAM)&"", MultiSubnetFailover=true])
in
Source
Unfortunately what you are trying to do is not possible. Currently the parameters in a query can only be changed at "design time" (while editing in Power BI Desktop) and cannot be changed dynamically while viewing a report.
One other approach would be to include all the rows from your User table, then apply the url filter using the filter= syntax as documented here https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-url-filters
Another approach could be to look at using Row Level Security to filter the user table, but you would need to have some way of mapping to a login to one of your user ids in order to do that.
4 Replies
Unfortunately what you are trying to do is not possible. Currently the parameters in a query can only be changed at "design time" (while editing in Power BI Desktop) and cannot be changed dynamically while viewing a report.
One other approach would be to include all the rows from your User table, then apply the url filter using the filter= syntax as documented here https://docs.microsoft.com/en-us/power-bi/collaborate-share/service-url-filters
Another approach could be to look at using Row Level Security to filter the user table, but you would need to have some way of mapping to a login to one of your user ids in order to do that.
Thanks for the reply.
My projet is "replace SSRS with PBIRS and betefit from pbix reports".
This limitation is very impactful in our design.
Option 1 means that I cant filter directly in query (hope reporting teams produce perfomant query)
Option 2 : actually we plan to use only one "readonly user" in PBIRS and manage application security in a "proxy server app". PBIRS open to external user...
Not really a funny projet
Anonymous wrote:
Option 2 : actually we plan to use only one "readonly user" in PBIRS and manage application security in a "proxy server app". PBIRS open to external user...
Yes, this would rule out using RLS. Although you would want to make sure you have done plenty of prototyping to make sure you can get this to work. I've seen lots of posts on this forum where people of had issues that they have not been able to resolve when trying to using custom authentication with PBIRS and PBIX based reports.
Anonymous wrote:
Option 1 means that I cant filter directly in query (hope reporting teams produce perfomant query)
This is true, but if you have chosen to use Direct Query you have already given up most of the control of how queries are generated. In the example you have given a visual level filter on the Users table should cause Direct Query to generate a similar SQL query with a similar WHERE clause to the one you had coded.
If you need very fine control over the queries then you might need to keep some of your reports as RDL reports where you have full control over that.
Hi Anonymous ,
Whether the advice given by d_gosbell has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.
Looking forward to your feedback.
Best Regards,
Henry
