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

[miken72]

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

[ibarrau]

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,

[miken72]

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)?

[ibarrau · 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,