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

Philip Østergaard Braun 0 Reputation points
2024-09-23T14:04:55.0333333+00:00

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

Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,899 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,284 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. MikeyQiaoMSFT-0444 3,025 Reputation points
    2024-09-24T09:49:57.87+00:00

    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.


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.