cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

How to use Filters in database queries?

Good.

I need to use the From/Until I select the query user in SQL syntax to fetch data.

You could initially run the query to the SQL server by taking the last 30 days as a default search.

But then the user must select other Ranges From/To and I need to take those values to rerun the query to the database with the new date range...

How could I do that?

Best regards

13 REPLIES 13
Highlighted
Post Patron
Post Patron

Re: How to use Filters in database queries?

I'm not completely sure what you want to accomplish, but from what I am understanding, you should load all of the data for all dates.  Then, use the slicer visual with your dates to select the date range you want to show in your report.

Highlighted
Helper II
Helper II

Re: 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

Highlighted
Post Patron
Post Patron

Re: How to use Filters in database queries?

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

Highlighted
Helper I
Helper I

Re: How to use Filters in database queries?

I assume you're mode you are fetching the data is set to DirectQuery. Is that the case?

If so, then you simply need to set up your visualization along with the date slicer. PowerBI will automatically append your query with the approproate WHERE clause to narrow your query.


Keep in mind that Power BI does have a 1,000,000 row limit on data returned from a database when in DirectQuery mode. It doesn't sound like you will be anywhere close to that.

Also, for performance reasons, make sure your filtered query can return in < 5 seconds.

Highlighted
Helper II
Helper II

Re: How to use Filters in database queries?

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

Highlighted
Helper II
Helper II

Re: How to use Filters in database queries?

Hello @jptak

Thank you for your answer..

I'm sando QueryDirect..

How do I do/define this that you tell me? you just need to set up the display along with date segmentation

Apologies but I'm new to PowerBI and I'm trying to migrate my reports made at Reporting Services to use the full power of PowerBI in handling information...

Best regards

Highlighted
Post Patron
Post Patron

Re: How to use Filters in database queries?

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.

Highlighted
Post Patron
Post Patron

Re: How to use Filters in database queries?

@GustavO65 

Also, be aware that I am writing in English and you are writing in Spanish. The Power BI forum is translating for us and there may be some errors.

Highlighted
Helper II
Helper II

Re: How to use Filters in database queries?

Hi @jstorm 

I´ll write in english!

As I told you I have been creating a powerBI report using "Import" instead of DirectQuery.
But I don´t have the last information for my fact table..
At the moment I don´t know why..
In my SQL Server I have information until today..
In PowerBi I have information until 04-29-2020 (the day I made the change from Direct Query to Imported)

 

refresh.PNG

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors