How to disable auto-refresh in Desktop

[intrasight]

I am querying on-prem SQL Server. I use "Import" - can't use DirectQuery because of M and DAX limitations. But there are a couple million rows in the table I import, and as I work it sometimes does a full refresh from SQL which is a problem. How can I disable the refresh and basically work with just the data I have imported?

[felyoubi]

Hi,

The simple answer is that PBI Desktop doesn't support auto-refresh, it's always on-demand until you hit Refresh. Perhaps you keep changing your schema that prompts you to update your data model. If you think about it, it makes sense. Your in-memory schema gets out of sync and has to be refreshed, there is no way around until if you start with Direct Query. Where You finalize your schema then switch to Import and do your datamashups there.

Another venue to explore without having to kill yourself scripting in M or DAX is to use a SQL view, have it return Top N and when you are ready for primetime, you unleash the beast.

IMHO, couple of millions of records are still manageable by PBI Desktop.

Regards,

Fahd

[sdjensen]

While you are developing your model you could just add a "TOP xxx" function to you queries so you don't get all the data loaded into your model and when you are done developing you just remove the "TOP xxx" filter from your queries.

I would recommend only using this approach on your fact table (table containing your values) else you can't get some strange results.

That being said when you work with PBI Desktop it will never load data from the source again unless you tell it to do it by pressing refresh or if you change your query.

[intrasight]

"Top N" makes sense, but I'm not writing SQL. Can I express that in "M"? Will it then query fold back to SQL?

[Eric_Zhang · Accepted solution]

@intrasight

When importing data, you can apply filters in the "M".

When your development work finishes, you can undo the "Filtered Rows" step in the last picture and apply changes.

[sdjensen]

@Eric_Zhang - would that kind of filter effect the query agains the SQL database or will the query first load all data and then filter some of the data away?

[Eric_Zhang]

---

@sdjensen wrote:

@Eric_Zhang - would that kind of filter effect the query agains the SQL database or will the query first load all data and then filter some of the data away?

---

It sends a re-orgnized query applying the filter to SQL database, based on my test in SQL Server profiler.