Semantic Model not working as expected

[dml5055]

Hi,

The semantic model is not working as expected in Excel (Analyze in Excel).  I have two fields that could map 1:1 to each other location/cost center, and it seems to behave properly in Desktop and Explorer Data in a browser.  However, when I try to do the same thing in excel through the Pivot, I'm getting a Cartesian join instead.

I just noticed this recently, and nothing material has changed in the model.

Thanks

[GilbertQ]

Hi @dml5055 

Just to reiterate what the others are saying is when you don't put a measure in using analyze and Excel, it will show you all the options. As soon as you put in a measure it will only show you those values where a measure is valid.

[Olufemi7 · Accepted solution]

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-excel
  
  

• Power 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
  

[dml5055]

Thanks.  I was unsure of the behavior between the PBI model and the semantic model.  this helped clear it up.

[cengizhanarslan]

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.

[GeraldGEmerick]

@dml5055 There is not enough information here to determine what is going on. Is there any chance that you could possibly provide additional information and perhaps sample data to recreate the scenario?