Query loads data slowly in Power BI desktop and online

[cewjr9842]

I am fairly new to PBI but usually try to research via the frum and google before i post here but I have a SQL query that i use to pull applicaiton decision data and load it into a table that shows the application decision time in totals as seen below. It is a direct query and uses that data to sort by day of the month and loads totals every 15 minute increments.

My concerns and questions are below:

1. Is if there is a way to load only the new data since it takes the query forever to populate the newest times? 

It pulls new data every 15 minutes and the table takes forever to load since its roughyl about 400,000 rows or so. 

2. is there a way to filter the data,  so that it will show the newest day instead of having to select it via the filter every morning.

3. FOr some reason when I move to PBI Online, i dont have the Scheduled cache refresh option in the settings for this dataset as it is a directi query but i thought schedule refresh would refresh the data in a set allotted time frame with as many refresh as the admin set for them but i dont have this option and was wondering why? I wanted the data to refresh so that the report stayed up to date. I have other reports that have it but not this one and was wondering if I missed something.

Thanks and your help is surely appreciated!!!!

[deldersveld]

1) Since you said you're using DirectQuery, there is no way to pull only new data--Power BI executes the query at the time the report is run and does not import it. If you used an Import model with incremental refresh, you could load only what's changed. This is only available currently for Premium in a workspace setup with dedicated capacity though. 2) Look into the Relative Date Filter 3) You wouldn't be able to schedule a refresh in DirectQuery mode but should if there are other tables in the model that are in Import mode.

[cewjr9842]

@deldersveld Thanks for your reply. I changed over to import . Also we are currently using the premium version, They just upgraded it last week. On the #2 reposnse you stated Relative Date Filter, but the way my data is pulled, its being sorted by day of month wichi is under the app_day column below,

My Table:

This is also the measure I am using to gather this data:

for the "sum of N":

Sum of n = 
SWITCH (
    TRUE (),
    SELECTEDVALUE ( Range[Range] ) = "Missing", CALCULATE ( 
        SUM ( 'Table'[n] ), 
        'Table'[time_bucket] = -1 
    ),
    SELECTEDVALUE ( Range[Range] ) = "<30", CALCULATE ( 
        SUM ( 'Table'[n] ), 
        'Table'[time_bucket] <= 30 
    ),
    SELECTEDVALUE ( Range[Range] ) = "30-60", CALCULATE (
        SUM ( 'Table'[n] ),
        'Table'[time_bucket] > 30
            && 'Table'[time_bucket] <= 60
    ),
    SELECTEDVALUE ( Range[Range] ) = "1-2 min", CALCULATE (
        SUM ( 'Table'[n] ),
        'Table'[time_bucket] > 60
            && 'Table'[time_bucket] <= 120
    ),
    SELECTEDVALUE ( Range[Range] ) = ">2 min", CALCULATE (
        SUM ( 'Table'[n] ),
        'Table'[time_bucket] > 120
            && 'Table'[time_bucket] <= 2400
    ),
    SELECTEDVALUE ( Range[Range] ) = ">20 min", CALCULATE ( 
        SUM ( 'Table'[n] ), 
        'Table'[time_bucket] > 2400 
    ),
    SELECTEDVALUE ( Range[Range] ) = BLANK (), 
    SUM ( 'Table'[n] )
)

The app_day is what I want to always auto populate or show up. So since the 15th is today, I wanted it to auto refresh and show up without having to select it from the filter, as seen in the screen shot above.  I was under the understanding that to use the relative date filter, you had to have a date column but I currently don't and if I needed to update my measure, to reflect a date I am totally lost on it. Also in trying to setup the incremental refresh, I thought I needed a range start and end date also. Not sure if it was possible to do a incremental refresh by value instead, but from what I been reading it seems it had to be a date on the incremental refresh and the relative date filter.

Any Suggestions?