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
DNicoll
New Member

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 5
DNicoll
New Member

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.

 

      

Hi @DNicoll ,

 

How do you define the required dates? Dates that exist in MS SQL?

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

I was hoping the user coupl enter them. I had them hrd coded when I was first investigating the issue. 

Never post before coffee! I was hoping the user could enter them. I had them hard coded when I was first investigating the issue. 

 

PS I can't see how you correct a post.

TomMartens
Super User
Super User

 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=Dy...

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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.