Forum Discussion
Fabric Warehouse Semantic Model with Excel Pivot – Recommended Approach for Excel-Centric Reporting?
Hello everyone,
We are currently migrating from SAP BW to Microsoft Fabric.
In our SAP landscape, business users mainly worked with Analysis for Office (AfO) and SAP Analytics Cloud (SAC) for reporting and analysis. In the Microsoft ecosystem, our target architecture is based on Fabric Warehouse / Semantic Models, Power BI, and Excel Pivot Tables.
While Power BI works well for many scenarios, we still have a significant number of users who prefer Excel-based reporting and ad-hoc analysis.
Our experience with Excel Pivot Tables connected to a Fabric Semantic Model has been mixed:
- The user experience is considerably less intuitive compared to Analysis for Office.
- Filtering large dimensions is cumbersome.
- Most importantly, negative filtering becomes problematic when a dimension contains more than 10,000 members, due to Excel's filter limitations.
My questions are:
- What is Microsoft's recommended approach for organizations that have a strong Excel reporting culture?
- For organizations migrating from SAP BW, is Excel Pivot Tables on top of Fabric Semantic Models the recommended replacement for SAP Analysis for Office?
- How do other organizations handle large dimensions and complex filtering requirements in Excel when using Fabric Semantic Models?
- Are there alternative Microsoft solutions or best practices that provide a better Excel-centric reporting experience?
We would be very interested in hearing how others have addressed this challenge during SAP BW → Fabric migrations.
Thank you for your insights.
Greetings
Ron
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
6 Replies
v-sgandrathiCommunity Support
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,
SahasraHi v-sgandrathi
thank you for the great discussion and your support. I will now proceed with the official Microsoft support ticket route and investigate the issue further through that channel.
Thanks again for your help and insights.
Best regards,
Ron
- v-sgandrathi
Hi RonStrasser,
This is a common challenge for organizations moving from SAP BW to Microsoft Fabric, especially when users are accustomed to the Analysis for Office experience.
1. What is Microsoft's recommended approach for organizations with a strong Excel reporting culture?
Microsoft's recommended approach is typically to centralize business logic, measures, hierarchies, and security in a Fabric semantic model and allow users to consume that model through both Power BI and Excel. This provides a governed semantic layer while still supporting Excel-based analysis.
2. For organizations migrating from SAP BW, is Excel Pivot Tables on top of Fabric Semantic Models the recommended replacement for SAP Analysis for Office?
Excel Pivot Tables (including Analyze in Excel) are generally the closest Microsoft equivalent to Analysis for Office. However, they are not a feature-for-feature replacement. Organizations migrating from SAP BW often notice differences in hierarchy navigation, member selection, filtering capabilities, and the overall user experience when working with large dimensions.
3. How do organizations handle large dimensions and complex filtering requirements?
For dimensions with thousands of members, many organizations adopt a combination of the following practices:
Create business-friendly hierarchies to reduce the number of members users need to browse.
Introduce grouping attributes or reporting dimensions that simplify filtering.
Use inclusion-based filtering whenever possible rather than excluding large numbers of members.
Build report-specific perspectives or curated semantic models for particular business areas.
Move highly complex filtering and exploration scenarios to Power BI, where slicers and filtering options are generally more flexible.The limitation you mentioned around negative filtering on dimensions with more than 10,000 members is largely an Excel limitation, so semantic model design can help but may not completely eliminate the challenge.
4. Are there alternative Microsoft solutions or best practices for Excel-centric reporting?
At present, Microsoft does not offer a direct equivalent to SAP Analysis for Office. Most successful SAP BW to Fabric migrations adopt a hybrid approach:
Excel for ad-hoc analysis, reconciliations, and spreadsheet-driven reporting.
Power BI for interactive analysis, large-dimension exploration, advanced filtering, and dashboarding.
In practice, many organizations find that trying to replicate every Analysis for Office scenario in Excel can be difficult. Instead, they use the Fabric semantic model as the common business layer and allow users to choose between Excel and Power BI based on the reporting requirement.Useful references:
Analyze in Excel: Create Excel Workbooks with Refreshable Power BI Data - Power BI | Microsoft Learn
Semantic Models in Fabric: Power BI Semantic Models - Microsoft Fabric | Microsoft LearnRegards,
Sahasra.
Hello v-sgandrathi
thank you for the detailed response.
To clarify the negative filtering issue: the problem occurs exclusively for dimensions containing more than 10,000 distinct values. When a negative filter (e.g., "Exclude selected items") is applied in Excel Pivot Tables connected to a semantic model, the filtering result appears to be incorrect. Significantly more records are excluded than would be expected based on the actual filter criteria.
As a consequence, the resulting figures are incorrect, which introduces a substantial business risk, as users may unknowingly make decisions based on incorrect filtering behavior.
Is this a known limitation or issue with Excel Pivot Tables against semantic models, particularly when working with dimensions exceeding 10,000 distinct values? Are there any documented workarounds, fixes, or best practices for handling negative filtering on large dimensions?
Thank you for your support.
Regards,Ron
- v-sgandrathi
Hi 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
-
