Hide Excel Pivot Table Rows Using Sets And Then Edit The MDX – 2367

If you need to hide some rows in an Excel pivot table, you can Create a Set Based on Row Items.

This is a rarely used feature: First, it is only available if you choose Add This Data To The Data Model.

Once you create a set, it is a bit strange to remove rows. And if you add data later, you have to go back in to edit the set. Excel offers to let you edit the MDX, but what is MDX?

Thanks to David Jefferson who provided a comment to a YouTube video, he shows how to use .Children to refer to all of the part numbers along a dimension. This is far more flexible.

Table of Contents
(0:00) What is MDX?
(0:37) Pivot table based on Data Model
(1:40) Hiding rows using a Set
(2:51) MDX Defined
(3:28) Editing MDX
(4:40) Adding new data & Refresh