Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

1 ACCEPTED SOLUTION
d_gosbell
Super User
Super User

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.

View solution in original post

4 REPLIES 4
v-henryk-mstf
Community Support
Community Support

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

d_gosbell
Super User
Super User

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.

Anonymous
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.