Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
intrasight
Helper IV
Helper IV

How to disable auto-refresh in Desktop

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?

1 ACCEPTED SOLUTION

@intrasight

 

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

 

1.PNG2.PNG3.PNG

 

 

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

View solution in original post

6 REPLIES 6
felyoubi
Resolver I
Resolver I

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
Solution Sage
Solution Sage

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.

/sdjensen

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

@intrasight

 

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

 

1.PNG2.PNG3.PNG

 

 

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

@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?

/sdjensen


@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.