Excel Pivot Table Filters being checked

[TDouglass]

Hello,

Looking for any links or feedback to help with the following issue:

We have a pivot table in Excel that is based on a cube that both Excel and Power BI use.  I will also post this on the Excel forum.

We have a filter on product to exclude a particular product number.  If we had a refresh last month and let's say there was no sales in August for product XYZ, the filter in the pivot details is unchecked.  When the cube is rebuilt and the pivot table is refreshed, we would now have sales for product XYX, but it is still unchecked from the last run.  Does anyone happen to know if there is an approach that we can do in macro's that would look at the data in the current cube data set and automatically check the items we want?

Thanks, Tina

[Anonymous]

Hi @TDouglass ,

Sounds like you want incremental refresh. Please kindly refer to

Incremental refresh for Power Query and Azure Data Lake data sources - Dynamics 365 Customer Insight...

Incremental refresh for data sources based on Power Query or Azure Data Lake provides the following advantages:

Faster refreshes - Only data that has changed gets refreshed. For example, you might refresh only the past five days of a historical dataset.
Increased reliability - With smaller refreshes, you don't need to maintain connections to volatile source systems for as long, reducing the risk of connection issues.
Reduced resource consumption - Refreshing only a subset of your total data leads to more efficient use of computing resources and decreases the environmental footprint.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TDouglass]

Stephen, The source of our data is an analysis services cube that is rebuilt every night. We don't build the data model via Power Query.  Not sure if that changes your reply or not.