Forum Discussion
How to use Filters in database queries?
Hello @jstorm
Thank you for your answer..
The problem is that the table on which I should base the query has 2,000,000 records.. When in fact a user's query needs only 5% or 6% of them...
I find it very unreconventional to have to take the 2,000,000 records when maybe I could trigger a much narrower query...
Best regards
I see. Do you have date ranges that users are interested in within a general time range or want users to be able to select any date range from the 2 million records? For example, if the data covers the last 10 years, you could filter to only the last two years, if that's what's relevant to the end user.
Another thing to keep in mind is if you need to use DirectQuery at all. Do I need to report your data in real time or in near real time? If not, you must switch to Import. Your reports will run much better and the number of records will not matter almost as much. It will also significantly reduce the impact on the SQL server.
Consider reviewing this documentation on query types.
https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about
Hello @jstorm
In principle I'm trying to use DirectQuery (I have a report armed with "Import" and the data on it is not being updated..)
Users have to select any date range....
It is not extremely important and/or critical that you report the data in real time. I could use "Import".. (I would have to finish viewing/resolving because the consultation I have in the armed office is not refreshing the information when I have created the task on the PoweBI Server to keep it in sync..)
Best regards
I would highly recommend switching to Import. DirectQuery has many limitations and should only be used when necessary. With Import, after you publish the report to PBI online, you should be able to set the refresh to occur up to 5 times per day. Spaced correctly, you could have fresh data every ~4 hours. To switch to import, open the 'Model' view, select the tables one by one in the fields section on the right, then expand 'Advanced'. You can change the 'Storage Mode' to Import. Try this with a copy of your current report to test it out. With Import, you can have millions of records no problem.