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

Query parameters accessible by end-users of Web-published rerports?

Hello,

 

We have several reports that read from a very large dataset. 
We are building in RLS (Row Level Security) so that a user can only see the results that they are allowed to see. That is working fine.

 

The problem is that we do not want to load up the whole dataset when the report is run, but allow the end-user of the Web-published PowerBI report to select the data they want to see results from by using parameters / filters, for example. if they want to see only 2020 results so they choose "2020" in a filter/drop-down, this value is picked up by the query parameter, and the direct Query of the report pulls only 2020 data. 


Does PowerBI allow this yet? Last I saw from back in 2018 was that this functionality was not available.

Thanks in advance

1 ACCEPTED SOLUTION

I haven't seen this feature in the roadmap for a short term. So would say no. If you still want to read about it this article shows a little more: https://blog.crossjoin.co.uk/2020/10/25/why-im-excited-about-dynamic-m-parameters-in-power-bi/

On the other hand you can handle this things using import mode. You can load both years of data and filter it in memory. Of course it depends on the amount of data and licenses, but usually I think that import mode can handle more features.

Regards,


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

View solution in original post

3 REPLIES 3
ibarrau
Super User
Super User

Hi. I think you are talking about dynamic M query parameters. Those are parameters for direct query to make the query change with the change of a slicer.

You can read about it here: https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters

You can also check a video here: https://www.youtube.com/watch?v=hrPbf06aF9o

I hope that helps,


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

Hello ibarrau, and thanks for your reply,

I've started looking into it and got my hopes up, but then saw that - as far as I can see - M queries only work with NoSQL M (MUMPS) datasources. From what I have just read - and I hadn't heard of M databases till about 30 minutes ago -  M databases like GT.M only run on Linux boxes. 

I like a challenge, but the project I am working on would get a somewhat more complicated if it was to involve setting up an M database,  and having regular updates of data converted and loaded from our Windows environment RDBMS to the M db so that the PowerBI reports could then read from that M db.

That said, I will ask around the company I work for - you never know...

Meanwhile, do you know if Microsoft has any plans to make a functionality similar to that of dynamic M query parameters for SQL Server?
It's their own product so you'd think it could be done - or maybe (fingers crossed) it has been implement during 2021 (since the articles I read were published)?

I haven't seen this feature in the roadmap for a short term. So would say no. If you still want to read about it this article shows a little more: https://blog.crossjoin.co.uk/2020/10/25/why-im-excited-about-dynamic-m-parameters-in-power-bi/

On the other hand you can handle this things using import mode. You can load both years of data and filter it in memory. Of course it depends on the amount of data and licenses, but usually I think that import mode can handle more features.

Regards,


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

Helpful resources

Announcements
Power BI Show Ep 4 Post Show Carousel.jpg

The Power BI Community Show

Shabnam Watson demos Incremental refresh & Hybrid Tables and Leila Etaati demos Charticulator.

PBI April Release 2022 768x460.png

Check it out!

Click here to read more about the April 2022 updates!

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors