如何在Excel透视表中显示无关联保费的AutoItem（Tabular模型场景）

Hey there! I get it—frustrating when your PivotTable isn’t showing all those AutoItems that don’t have associated premiums, even though you set up that 0 key in FactPremium. Let’s break down the fixes step by step:

1. Fix the Relationship Join Type in Your Tabular Model #

First, the most likely culprit is that your model is using an inner join between DimAuto and FactPremium by default, which filters out any DimAuto rows that don’t have a matching row in FactPremium. Here’s how to change it to a left outer join:

• Open your SSAS Tabular model in Visual Studio (or Tabular Editor).
• Locate the relationship between DimAuto and FactPremium (the one linking your AutoItem key).
• Right-click the relationship and select Properties.
• In the Join Type dropdown, pick Left Outer (this ensures all rows from DimAuto are kept, even if there’s no matching row in FactPremium).
• Save your model and redeploy it to your SSAS server.

2. Add a "Placeholder" DAX Measure #

Even with a left outer join, Excel PivotTables often hide rows that don’t have a valid value for the selected measure. To work around this, create a simple measure in your DimAuto table that doesn’t depend on FactPremium:

AutoItem Count = COUNTROWS(DimAuto)

Or an even simpler one if you just need to force visibility:

All AutoItems = 1

• Add this measure to your model, redeploy, then refresh your Excel data connection.
• Drag this measure into the Values area of your PivotTable. Now all AutoItems should show up, even those without premiums.

3. Adjust Excel PivotTable Field Settings (Optional) #

If you still don’t see the missing items after the above steps, tweak the field settings in Excel:

• In the PivotTable Fields pane, right-click the AutoItem field from DimAuto.
• Select Field Settings > Go to the Layout & Print tab.
• Check the box for Show items with no data and click OK.

This last step is often redundant if you’ve fixed the relationship and added the placeholder measure, but it’s a good fallback for edge cases.

内容的提问来源于stack exchange，提问作者SSAS developer