Forum Discussion
Get Data from Server within a dynamic date range
Hi, I see there is a post - Dynamic Data fetch trigger while changing the date range, with a similar question. It is fairly old so I was wondering if there was any update.
I have the same issue, I have 10 years worth of data. I want the user to be able to select a year say from some sort of data entry, possibly not a slicer, though that would be nice, and then do their analysis on the data fetched.
This is relatively easy in Excel using parameters, but I can't work out how to do it in Power BI, and can't see anything in the forums. I really do not want the user querying for the whole 10 years worth of data if I can avoid it. Unecessary load on the server and makes user response horribly slow.
Apologies if this is trivial, it's been a while since I set the Excel stuff up, Power BI is new to me, but I really like the power of some of the analysis features.
5 Replies
Hey DNicoll ,
depending on the data source you can use Dynamic M Paramters, but just a couple of datasources are supporting this feature at the current moment: https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters#:~:text=Dynamic%20M%20Query%20Parameters%20are%20currently%20in%20preview%2C,BI%20Desktop%20for%20the%20change%20to%20be%20enabled.
If you want to use DirectQuery, there are two options
- create a table that just contains the data you want to be accessed by the Power BI users and restrict the users to this table
- create a view on top of the table that restricts the data, to the proper timeframe, but of course this might limit the strain to the source system that much, depending on the source system you can create indexed or materialized views
Another solution, just import the data into a Power BI dataset and limit the users to this dataset. Then the do not have to query the original data source.
Hopefully, this provides some ideas on how to tackle your challenge.
Regards,
Tom
Hey Anonymous,
Thank for your prompt reply. I now realise I did not supply full information. I have 2 data sources, one MS SQL, which has lower data levels so less of a problem. The other is an ODBC connection to a large real time database. In excel, it is easy, a query that takes the dates required and passes as parameter to ODBC SQL. I really don't want to be involved in what data they select, so pulling fixed data into a BI is really not what I want.
May be need to dig deeper.
