Forum Discussion
Fabric Warehouse Semantic Model with Excel Pivot – Recommended Approach for Excel-Centric Reporting?
Hi RonStrasser,
Thanks for sharing the extra details and the reproducible example.
From the behavior you outlined:
- Excel Version 2508 (Build 19127.20622)
- Connection through Power BI semantic model
- The same exclusion filter returns the correct results in Power BI
- The incorrect results appear only in Excel when exclusion-based filtering is used on dimensions with more than 10,000 distinct members
- Excluding a single member removes a much larger portion of the dataset
This does not seem to match the expected behavior of a semantic model, especially since the same filter logic works correctly in Power BI. Since the issue can be reproduced and is specific to Excel, it looks more likely to be related to how Excel handles exclusion filters on large member sets than to the semantic model itself.
At the moment, I am not aware of any published Microsoft documentation that lists this as a known limitation. Because this could affect data accuracy and business decisions, I would suggest opening a Microsoft Support ticket and including:
- The Excel version and build information
- A sample semantic model, if possible
- Steps to reproduce the issue
- Comparison results showing the correct behavior in Power BI and the incorrect behavior in Excel
Create a Fabric and Power BI Support Ticket - Power BI | Microsoft Learn
As a temporary workaround, many organizations use inclusion-based filters or handle complex filtering in Power BI when working with high-cardinality dimensions. That said, those approaches would only reduce the impact and would not solve the underlying behavior you are seeing.
Regards,
Sahasra
Community SupportHi RonStrasser,
Thank you for the additional clarification.
Based on your description, the concern appears to be less about the usability of filtering large dimensions and more about the accuracy of the results returned when using negative filters on dimensions with more than 10,000 members.
I am not aware of any documented limitation that specifically states Excel Pivot Tables connected to a semantic model will return incorrect results when applying exclusion-based filters on dimensions of that size. If significantly more records are being excluded than expected, resulting in incorrect figures, that would not be considered expected behavior.
As a general best practice, organizations working with high-cardinality dimensions often:
Use inclusion-based filtering where possible rather than exclusion-based filtering.
Design hierarchies or grouped attributes in the semantic model to reduce the number of members users need to filter directly.
Create curated semantic models for specific business domains.
Use Power BI for complex filtering scenarios involving very large dimensions.
However, these approaches are design recommendations and would not necessarily explain or resolve the behavior you are seeing.
To help narrow down the root cause, could you confirm:
-
Which version of Excel is being used?
-
Whether the connection is established through Analyze in Excel?
-
Whether the same filter logic produces the expected results in Power BI against the same semantic model?
-
Whether the issue occurs consistently once the dimension exceeds a certain number of members?
Regards,
Sahasra
Hi v-sgandrathi
Thank you for your questions.- We are using Excel Version 2508 (Build 19127.20622).
- The connection is established via Data → Get Data → From Power Platform → From Power BI -> connect to semantic model. (after connect advanced work with PivotTable functionality)
- The same filter logic produces the expected results in Power BI against the same semantic model.
- Yes, the issue is easily reproducible.
As an example, we have a report containing approximately 180,000 rows. When applying a negative filter (excluding a single unique dimension member), only one row should be removed from the result set. Instead, approximately 80,000 rows disappear, leading to significantly incorrect figures.
Based on our testing, the behavior appears to be related to dimensions with more than 10,000 distinct members. The issue does not seem to be a usability limitation but rather an incorrect filtering result returned by Excel when exclusion-based filters are applied against the semantic model.
Regards
