Only showing the last X number of readings with a Direct Query connection

[ejwwelshboy]

Hello, I have some data in power BI that is connected to a SQL database via a Direct Query connection. I am having issues trying to only show the last X amount of readings, or reading over the last X amount of days. I tried using the Age function built into Power BI, but it would not work with a Direct Query connection. The time data that I am receiving is in the following format. 

6/17/2017 3:05:00 PM

Any advice on how to limit the readings shown on my graphs would be greatly appreciated. 

[MFelix]

Hi @ejwwelshboy,

If you want to limit the amount of data you are receiving from SQL try to use a parameter to pass along the number of dsays you want to deduct from your query, check this page on parameters for power bi and you can take some ideas.

Regards,

MFelix

[ejwwelshboy]

Hi @MFelix,

I don't think ising the parameters function would work in my case becase while I can use it to only show certain days I have no way to only show the last 2 days of data that is constantly being refreshed using the direct query connection. 

Thanks,

Ejwwelshboy

[MFelix]

Hi @ejwwelshboy,,

Can you please explain and show some example of what you want.

Regards,

MFelix

[ejwwelshboy · Accepted solution]

Thanks for the help @MFelix.

I was able to figure out a solution to my problem. I was able to use a SQL statement to only bring in the last 24 hours of data into Power BI.

The SQL statement that I used was

select Name, location, myDate from myTable where myDate between DATEADD(hh, -24, GETDATE()) and GETDATE()