why is filtering my pivot table is triggering a data update

[stigcorneer]

I have a pivot table in excel based on the Datamodel based on a SQL-database.

When I filter a column in the pivot table the datasource starts updating.
Why is this? Is there some way around it?

 

[Matt Allington]

Is it a direct connection to the SQL db, or does it have a local model. Both are possible.

 

[stigcorneer]

It is a direct connection from SQL DB to Power Pivot Datamodel

 

[Matt Allington]

Why is this? Is there some way around it?

Yes.
1. don't use direct connection
2. I think there is a "defer update" option (or similar) in the fields list that will wait until you click a button.

 

[stigcorneer]

Yes.
1. don't use direct connection
2. I think there is a "defer update" option (or similar) in the fields list that will wait until you click a button.

Ok, I was afraid of that...
Thanks

 

[stigcorneer]

I changed from Direct Connection to loading data into the Datamodel with PowerQuery.

But I still get a dataupdate everytime I apply a filtration on the Pivot table.
Is this avoidable?

 

[Matt Allington]

Something is wrong, I don’t know what. Data does not update when you change filtered items in a pivot table if the data is in import mode (data loaded). Maybe the model is out of sync (a guess). Try adding a random calculated column to any table in the data model. Simpler column:=1. Then save and go back to the spreadsheet and see if anything has changed. Sometimes this can trigger the model back into shape. Also check all the connection sources in PQ to make sure there are no rouge connections with direct query.

 

[stigcorneer]

Also check all the connection sources in PQ to make sure there are no rouge connections with direct query.

How would I recognize such rouge connections?

 

[Matt Allington]

using Data\existing connections. But now that I think about it, you can't have a live connection to a SQL database with Power Pivot anyway. Is it a SSAS Database? What exactly did you change?

 

[stigcorneer]

Well now it works.

It is a Azure DB not SSAS.
First I connectded directly from Power Pivot to the DB.
But now I changed it to loading data via Power Query.
It got better but not perfect.

Then I did as you said and added a =1 column and now it works like a charm.

Not really sure what part did the magic trick but as long as something did , I am happy.

Thank you!