Deselecting Members In An Excel PivotTable Leads To Missing Rows version 2

Question

Dear community,

I followed the steps from this guide (https://blog.crossjoin.co.uk/2017/09/01/obscure-mdx-month-deselecting-members-in-an-excel-pivottable-leads-to-missing-rows/?unapproved=131403&moderation-hash=76337d5c67f0ea37902cb9d9154329ce#comment-131403)

I have a pivot table that includes a dimension with more than 32000 rows and when I deselected one one attribute in the row filters it returns 32.000 rows because of the MDX run against the model. If I select "Include new filters in manual filter" it returns the correct amount of rows.

This can only be done when filtering rows. The same problem occurs if I try using the attribute in filters. The MDX changes to include all the members instead of using EXPECT.

How can this be solved?

Best regards,

Philip

Answer 1

Hi，Philip Østergaard Braun

As shown in the blog, by changing the field settings and selecting "Include new items in manual filter," Excel uses the Except() function to exclude the unselected members from the entire set. The "Include new items in manual filter" option may not be applicable to report filters.

I recommend using slicers to control what is visible in the PivotTable, instead of using the filter area.

Refer tohttps://learn.microsoft.com/en-us/analysis-services/multidimensional-models/mdx/mdx-query-and-slicer-axes-specify-the-contents-of-a-slicer-axis?view=asallproducts-allversions

Regards

Mikey Qiao

---

If you're satisfied with the answer, don't forget to "Accept it," as this will help others who have similar questions to yours.