SOLVED

Pivot table manual row label filter no longer allows expand/collapse hierarchy

%3CLINGO-SUB%20id%3D%22lingo-sub-1893126%22%20slang%3D%22en-US%22%3EPivot%20table%20manual%20row%20label%20filter%20no%20longer%20allows%20expand%2Fcollapse%20hierarchy%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1893126%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20two%20fields%20under%20row%20labels.%26nbsp%3B%20Up%20until%20a%20couple%20of%20weeks%20ago%2C%20I%20was%20able%20to%20filter%20on%20both%20these%20fields%20using%20the%20manual%20row%20label%20filter.%26nbsp%3B%20Now%2C%20for%20some%20reason%2C%20I%20am%20unable%20to%20do%20so.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPreviously%2C%20upon%20clicking%20on%20the%20row%20label%20filter%2C%20the%20values%20in%20Field1%20appeared%20with%20the%20%22%2B%22%20symbol%20to%20the%20left%20of%20the%20checkbox.%26nbsp%3B%20Clicking%20on%20the%20%22%2B%22%20symbol%20allowed%20me%20to%20expand%20the%20filter%20list%20to%20reveal%20Field2%2C%20which%20appeared%20with%20checkboxes%20for%20inclusion%20and%20exclusion.%26nbsp%3B%20In%20other%20words%2C%20I%20could%20filter%20on%20both%20Field1%20%26amp%3B%20Field%202%20at%20once%2C%20while%20viewing%20the%20hierarchical%20relationship%20between%20them.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%2C%20when%20I%20click%20on%20the%20row%20label%20filter%20I%20still%20get%20the%20values%20in%20Field1%20with%20the%20%22%2B%22%20symbol%20and%20the%20checkboxes%2C%20however%2C%20when%20I%20click%20on%20the%20%22%2B%22%20symbol%20it%20disappears%20and%20nothing%20happens.%26nbsp%3B%20Previously%2C%20this%20behavior%20occurred%20only%20when%20the%20Field1%20label%20represented%20the%20sole%20instance%20of%20this%20data%20point%20in%20the%20source%20data.%26nbsp%3B%20I%20notice%20that%20at%20the%20top%20of%20the%20row%20filter%20drop%20down%20there%20is%20a%20field%20labeled%20%22Select%20Field%22.%26nbsp%3B%20This%20will%20change%20the%20field%20on%20which%20I%20can%20filter%2C%20and%20both%20Field1%20%26amp%3B%20Field2%20can%20be%20found%20here.%26nbsp%3B%20But%20lost%20is%20the%20ability%20to%20view%20the%20hierarchical%20relationship%20between%20Field1%20and%20Field2.%26nbsp%3B%20What%20I%20am%20asking%2C%20in%20effect%2C%20is%20how%20I%20move%20this%20%22Select%20Field%22%20menu%20into%20the%20filter%20list%20itself.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20has%20happened%3F%26nbsp%3B%20I%20have%20a%20feeling%20that%20an%20update%20to%20Excel%20may%20have%20change%20something%2C%20or%20blown%20out%20a%20default%20setting.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20very%20much%20for%20any%20help%20you%20can%20offer.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1893126%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1894073%22%20slang%3D%22de-DE%22%3ESubject%3A%20Pivot%20table%20manual%20row%20label%20filter%20no%20longer%20allows%20expand%2Fcollapse%20hierarchy%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1894073%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F871118%22%20target%3D%22_blank%22%3E%40abwconsulting%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EI%20am%20certainly%20not%20the%20most%20suitable%20to%20give%20you%20an%20answer.%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EBut%20%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3Eif%20I%20can%20recommend%2C%20It%20can%20help%20us%20all%20if%20you%20upload%20an%20Excel%20file%20(without%20sensitive%20data)%2C%20no%20picture.%3C%2FP%3E%3CP%3EEven%20if%20it%20is%20said%20that%20a%20picture%20can%20say%20a%20thousand%20words%2C%20it%20is%20certainly%20not%20in%20the%20case%20of%20Excel%2C%20on%20the%20contrary%20in%20some%20cases.%3C%2FP%3E%3CP%3EYou%20could%20get%20a%20precise%20solution%20much%20faster%20with%20a%20file%20(w%2Fout%20sensitive%20data).%3C%2FP%3E%3CP%3EThis%20would%20also%20be%20a%20blessing%20for%20all%20of%20us%2C%20as%20we%20can%20understand%20the%20problem%20much%20better%2C%20a%20win-win%20situation%20for%20everyone.%3C%2FP%3E%3CP%3E*%20Knowledge%20of%20Excel%20version%20and%20the%20operating%20system%20are%20a%20must%20if%20you%20want%20to%20get%20a%20reasonable%20solution%20(Example%3A%20office%20version%20e.g.%202016%20or%202019%20or%20365%20web%20or%20365%20pro%2C%20etc)%20and%20your%20operating%20system%20(e.g.%20Win10%20(2004)%2C%20Win%2010%20(1903)%2C%20Mac%2C%20etc.).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20understanding%20and%20patience%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1894266%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20table%20manual%20row%20label%20filter%20no%20longer%20allows%20expand%2Fcollapse%20hierarchy%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1894266%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F871118%22%20target%3D%22_blank%22%3E%40abwconsulting%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20in%20your%20data%20model%20Field1%20and%20Field2%20are%20in%20hierarchy%20and%20hierarchy%20is%20added%20to%20rows%20in%20PivotTable%2C%20it%20shall%20work%20as%20%22before%22%26nbsp%3B%20in%20your%20case%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20697px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F234075i98E33F4888EFAA5E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIf%20to%20add%20to%20PivotTavle%20fields%20themselves%2C%20not%26nbsp%3Bhierarchy%2C%20behavior%20will%20be%20as%20what%20you%20have%20%22now%22%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20567px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F234076i225DD544957EF619%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1897713%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20table%20manual%20row%20label%20filter%20no%20longer%20allows%20expand%2Fcollapse%20hierarchy%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1897713%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20so%20much%2C%20Sergei!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20to%20clarify%2C%20the%20creation%20of%20the%20hierarchy%20takes%20place%20in%20the%20Power%20Pivot%20window%2C%20not%20in%20the%20Power%20Query%20Editor%2C%20yes%3F%26nbsp%3B%20At%20least%20this%20is%20where%20I%20was%20able%20to%20do%20it%2C%20following%20your%20instructions.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20could%20swear%20that%20when%20I%20first%20created%20the%20pivot%20I%20did%20not%20create%20a%20hierarchy%2C%20but%20the%20pivot%20displayed%20a%20hierarchical%20filter%20nevertheless.%26nbsp%3B%20It%20does%20seem%20as%20though%20something%20has%20changed%20in%20Excel%20since%20that%20time.%26nbsp%3B%20(Indeed%2C%20I%20received%20at%20least%20one%20Office%20365%20update%20since%20that%20time.)%26nbsp%3B%20Regardless%2C%20this%20now%20works%2C%20and%20you%20have%20my%20sincere%20thanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20regards%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETom%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I have two fields under row labels.  Up until a couple of weeks ago, I was able to filter on both these fields using the manual row label filter.  Now, for some reason, I am unable to do so.  

 

Previously, upon clicking on the row label filter, the values in Field1 appeared with the "+" symbol to the left of the checkbox.  Clicking on the "+" symbol allowed me to expand the filter list to reveal Field2, which appeared with checkboxes for inclusion and exclusion.  In other words, I could filter on both Field1 & Field 2 at once, while viewing the hierarchical relationship between them.  

 

Now, when I click on the row label filter I still get the values in Field1 with the "+" symbol and the checkboxes, however, when I click on the "+" symbol it disappears and nothing happens.  Previously, this behavior occurred only when the Field1 label represented the sole instance of this data point in the source data.  I notice that at the top of the row filter drop down there is a field labeled "Select Field".  This will change the field on which I can filter, and both Field1 & Field2 can be found here.  But lost is the ability to view the hierarchical relationship between Field1 and Field2.  What I am asking, in effect, is how I move this "Select Field" menu into the filter list itself.  

 

What has happened?  I have a feeling that an update to Excel may have change something, or blown out a default setting.

 

Thanks very much for any help you can offer.

6 Replies
Highlighted

@abwconsulting 

I am certainly not the most suitable to give you an answer.

But if I can recommend, It can help us all if you upload an Excel file (without sensitive data), no picture.

Even if it is said that a picture can say a thousand words, it is certainly not in the case of Excel, on the contrary in some cases.

You could get a precise solution much faster with a file (w/out sensitive data).

This would also be a blessing for all of us, as we can understand the problem much better, a win-win situation for everyone.

* Knowledge of Excel version and the operating system are a must if you want to get a reasonable solution (Example: office version e.g. 2016 or 2019 or 365 web or 365 pro, etc) and your operating system (e.g. Win10 (2004), Win 10 (1903), Mac, etc.).

 

Thank you for your understanding and patience

 

 

Nikolino

I know I don't know anything (Socrates)

Highlighted
Best Response confirmed by abwconsulting (New Contributor)
Solution

@abwconsulting 

If in your data model Field1 and Field2 are in hierarchy and hierarchy is added to rows in PivotTable, it shall work as "before"  in your case

image.png

If to add to PivotTavle fields themselves, not hierarchy, behavior will be as what you have "now"

image.png

Highlighted

@Sergei Baklan 

 

Thanks so much, Sergei!

 

Just to clarify, the creation of the hierarchy takes place in the Power Pivot window, not in the Power Query Editor, yes?  At least this is where I was able to do it, following your instructions.

 

I could swear that when I first created the pivot I did not create a hierarchy, but the pivot displayed a hierarchical filter nevertheless.  It does seem as though something has changed in Excel since that time.  (Indeed, I received at least one Office 365 update since that time.)  Regardless, this now works, and you have my sincere thanks.

 

Best regards,

 

Tom

Highlighted

@abwconsulting 

Tom, yes, that's Power Pivot, hierarchy exists in data model. Date/Time hierarchy is added automatically if only you didn't disable this setting, other ones you may create manually.

image.png

 

Highlighted
Highlighted