Forum Discussion
Semantic Model not working as expected
Hi dml5055,
What you’re seeing in Analyze in Excel is expected behavior. Excel PivotTables don’t automatically enforce semantic model relationships the way Power BI visuals do. When you place two dimension fields (like Location and Cost Center) into rows/columns without a measure, Excel often produces a Cartesian product (every combination), which is why it looks correct in Power BI Desktop and the browser but not in Excel.
What to do
Use a measure in Values: Add a measure (e.g., SUM(Cost) or COUNTROWS(FactTable)) to the Values area. Measures anchor the PivotTable and cause filters to propagate according to the model’s relationships, avoiding the cross-join effect.
Model-side options: If Location and Cost Center are truly 1:1, consider a hierarchy or a combined key/column so Excel treats them as a single dimension when slicing.
Validate relationships: Confirm the relationship is active, correctly cardinality-defined, and has the intended filter direction. If you rely on bi-directional filtering, ensure it’s necessary and consistent with your model design.
Stay current: Make sure you’re using the latest Power BI semantic model experience in Excel/Analyze in Excel Microsoft has shipped connectivity and experience improvements that affect PivotTables and field discovery.
Connect Excel to Power BI semantic models (overview, connectivity improvements, and experience details) https://learn.microsoft.com/en-us/power-bi/collaborate-share/service-connect-power-bi-datasets-excelPower BI semantic model experience in Excel (discover models, PivotTables, considerations and limitations) https://learn.microsoft.com/en-us/power-bi/collaborate-share/service-connect-excel-power-bi-datasets
Design refreshable reports in Excel with Power BI data (PivotTables behavior when connected to semantic models) https://learn.microsoft.com/en-us/power-bi/collaborate-share/service-analyze-power-bi-datasets-excel
Nothing likely changed in your model—the difference is how Excel interprets it. Power BI visuals respect relationships by default; Excel PivotTables typically require a measure (or a hierarchy/combined dimension) to avoid Cartesian joins
Hi dml5055,
What you’re seeing in Analyze in Excel is expected behavior. Excel PivotTables don’t automatically enforce semantic model relationships the way Power BI visuals do. When you place two dimension fields (like Location and Cost Center) into rows/columns without a measure, Excel often produces a Cartesian product (every combination), which is why it looks correct in Power BI Desktop and the browser but not in Excel.
What to do
Use a measure in Values: Add a measure (e.g., SUM(Cost) or COUNTROWS(FactTable)) to the Values area. Measures anchor the PivotTable and cause filters to propagate according to the model’s relationships, avoiding the cross-join effect.
Model-side options: If Location and Cost Center are truly 1:1, consider a hierarchy or a combined key/column so Excel treats them as a single dimension when slicing.
Validate relationships: Confirm the relationship is active, correctly cardinality-defined, and has the intended filter direction. If you rely on bi-directional filtering, ensure it’s necessary and consistent with your model design.
Stay current: Make sure you’re using the latest Power BI semantic model experience in Excel/Analyze in Excel Microsoft has shipped connectivity and experience improvements that affect PivotTables and field discovery.
Connect Excel to Power BI semantic models (overview, connectivity improvements, and experience details) https://learn.microsoft.com/en-us/power-bi/collaborate-share/service-connect-power-bi-datasets-excelPower BI semantic model experience in Excel (discover models, PivotTables, considerations and limitations) https://learn.microsoft.com/en-us/power-bi/collaborate-share/service-connect-excel-power-bi-datasets
Design refreshable reports in Excel with Power BI data (PivotTables behavior when connected to semantic models) https://learn.microsoft.com/en-us/power-bi/collaborate-share/service-analyze-power-bi-datasets-excel
Nothing likely changed in your model—the difference is how Excel interprets it. Power BI visuals respect relationships by default; Excel PivotTables typically require a measure (or a hierarchy/combined dimension) to avoid Cartesian joins
Thanks. I was unsure of the behavior between the PBI model and the semantic model. this helped clear it up.
