Forum Discussion
Filter a directQuery with parameter and apply parameter change within URL call (PBIRS sept2021)
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.
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.