Pivot Table Default Filter

%3CLINGO-SUB%20id%3D%22lingo-sub-2870814%22%20slang%3D%22en-US%22%3EPivot%20Table%20Default%20Filter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2870814%22%20slang%3D%22en-US%22%3E%3CP%3EFound%20an%20old%20thread%20with%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2FSet-PivotTable-default-layout-options-efd8569c-f07a-43c1-9db2-4f2912a0f94e%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2FSet-PivotTable-default-layout-options-efd8569c-f07a-43c1-9db2-4f2912a0f94e%3C%2FA%3E%3C%2FP%3E%3CP%3Eshowing%20setting%20defaults%20for%20Pivot%20Tables%2C%20but%20after%20a%20short%20time%20reviewing%20it%20looks%20to%20be%20more%20layout%20defaults%20rather%20than%20filter%20defaults.%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20this%20is%20probably%20not%20an%20option%20-%20logically%2C%20absent%20data%20can't%20be%20filtered.%20But%2C%20is%20there%20a%20way%20to%20set%20filters%20to%20a%20specific%20value%20that%20I%20know%20will%20be%20in%20the%20data%20once%20I%20paste%20it%20into%20the%20table%3F%3C%2FP%3E%3CP%3EFor%20example%20-%20a%20labor%20file%20is%20blank%20until%20I%20download%20and%20manipulate%20the%20data%20(clearing%20out%20blanks%20and%20adjusting%20any%20%22N%2FA%22%20fields).%20Once%20in%20the%20correct%20format%20and%20free%20of%20invalid%20data%2C%20I%20copy%2Fpaste%20into%20the%20pivot%20tables%20data%20set.%20I%20know%20that%20%22Regular%20Worked%20Hours%22%20will%20be%20in%20the%20data%20set%20and%20I%20have%20several%20pivot%20tables%20for%26nbsp%3Bdifferent%20departments.%20Is%20there%20a%20way%20to%20set%20the%20default%20for%20each%20of%20these%20tables%20to%20only%20include%20Regular%20Worked%20Hours%20so%20all%20I%20need%20to%20do%20is%20refresh%20once%20the%20data%20is%20copied%20over%20to%20the%20table%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2870814%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2871615%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Table%20Default%20Filter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2871615%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1101340%22%20target%3D%22_blank%22%3E%40RandomPanda%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'd%20add%20data%20to%20data%20model%20and%20use%20DAX%20measures%20in%20PivotTables%20instead%20of%20implicit%20aggregations.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Found an old thread with https://support.office.com/en-us/article/Set-PivotTable-default-layout-options-efd8569c-f07a-43c1-9d...

showing setting defaults for Pivot Tables, but after a short time reviewing it looks to be more layout defaults rather than filter defaults. 

So, this is probably not an option - logically, absent data can't be filtered. But, is there a way to set filters to a specific value that I know will be in the data once I paste it into the table?

For example - a labor file is blank until I download and manipulate the data (clearing out blanks and adjusting any "N/A" fields). Once in the correct format and free of invalid data, I copy/paste into the pivot tables data set. I know that "Regular Worked Hours" will be in the data set and I have several pivot tables for different departments. Is there a way to set the default for each of these tables to only include Regular Worked Hours so all I need to do is refresh once the data is copied over to the table?

3 Replies

@RandomPanda 

I'd add data to data model and use DAX measures in PivotTables instead of implicit aggregations.

English? Example? Consider me new to Pivot Tables. I'm editing tables that were in place before I started. 

@RandomPanda 

Afraid that won't work then. If you already use cached PivotTables you can't convert them, need to build new PivotTables adding data to data model (check box at bottom left when you create PivotTable).  DAX basics are described here QuickStart: Learn DAX Basics in 30 Minutes (microsoft.com) but that's definitely not what you may copy/paste with small adjustments.

 

As for the cached PivotTable I don't know how to predefine filters before filtered data is added, sorry.