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
1) In Excel this setting can force the PivotTable to display all combinations of row/column members (which looks exactly like a Cartesian join), even when the measure is blank for most combinations.
In the PivotTable:
Right-click the field (e.g., Location) → Field Settings
Go to Layout & Print
Uncheck: Show items with no data
Do the same for the Cost Center field.
Also check:
PivotTable Options → Display
Ensure “Show items with no data on rows/columns” is off (if present in your Excel version).
2) Your measure returns 0 instead of BLANK()
A very common modeling pattern is IF(ISBLANK(...),0, ...).
In Power BI visuals, this can sometimes still “look fine” depending on visual settings, but in Excel pivots 0 is data, so Excel happily shows those combinations.