Pivot Table Issues

How to Show Zero Values in Power Pivot Charts While Preserving Slicers

Kushani Nimanthika Oct 14, 2026 870 views

Question details

The user needs to display items with zero results in a Power Pivot chart while ensuring the data model's slicer filtering continues to function properly.

How to Show Zero Values in Power Pivot Charts While Preserving Slicers

Product: Spreadsheet
Device & OS: not provided
Scenario: Creating a Power Pivot chart with a shared Team Name slicer where zero-value records need to be visible without overriding or breaking the slicer's filters.
Observed behavior: Adding '+0' to a DAX measure causes blank results to bypass the slicer filtering, showing all agents regardless of the selected team.

Before you start

Ensure your data model is correctly set up with established relationships between your main fact table and your lookup tables before modifying your DAX measures.

Solution 1Recommended

Use a DAX Formula Workaround to Return a Near-Zero Value

Replace the '+0' in your measure with a conditional formula that returns an infinitesimally small number, allowing the slicer to function normally while appearing as zero in the chart.

Adding +0 to a DAX measure forces the formula to return a value even when there is no data, which bypasses the slicer's filter context and shows all items. Returning a very small number like 1E-15 circumvents this issue.

Edit the Problematic Measure

Locate the measure that currently uses the +0 logic (e.g., SUM(AgentCallDetail[Refused])+0) in your Power Pivot Data Model.

Apply the SUMX and ISBLANK Workaround

Change the DAX formula to conditionally return a tiny fraction if blank. For example: =SUMX(AgentCallDetail, IF(ISBLANK(AgentCallDetail[Refused]), 1E-15, AgentCallDetail[Refused])).

Disable Empty Item Display

Right-click your PivotTable, select PivotTable Options, navigate to the Display tab, and ensure that 'Show items with no data on rows' is disabled.

Format the Output

Format the measure in your Pivot Chart to display as a standard number with zero decimal places so the 1E-15 value appears cleanly as 0.

Use a DAX Formula Workaround to Return a Near-Zero Value

Preserved Relationships: This method prevents the data model from ignoring the related TeamName table slicer, keeping your chart accurately filtered while displaying zero-activity items.

Solution 2

Adjust PivotTable Settings and Pull Slicer from the Fact Table

If you prefer not to use complex DAX formulas, you can adjust the standard PivotTable options and pull the slicer directly from the main data table.

Free Microsoft Office alternative

Need a simpler way to analyze data? Try WPS Office.

Complex data models and DAX formulas in Microsoft Excel can be overwhelming. WPS Spreadsheet offers a lightweight, intuitive, and highly compatible alternative for standard PivotTables and daily data analysis, completely free of charge.

Fully compatible with Microsoft Excel (.xlsx) formats and standard PivotTables.Lightweight installation with fast processing for large datasets.Familiar, easy-to-use interface that requires no steep learning curve.Completely free alternative for everyday data visualization and charting.

Frequently Asked Questions

Why does adding +0 to a DAX measure break my slicer?

Adding +0 forces the measure to return a value (zero) even when there is no underlying data for that combination of filters. This causes the PivotTable to display all items, effectively ignoring the slicer's filter context and breaking your data model relationships.

Can I just use the 'Show items with no data' setting instead of a DAX formula?

Yes, in standard PivotTables this often works. However, in Power Pivot Data Models, enabling this setting combined with certain measures can cause performance issues or display irrelevant dimension items. Using the 1E-15 workaround is often more precise for complex relationships.

How do I format a small DAX value to look like zero in my chart?

You can format the measure by selecting it in the Power Pivot window or PivotTable, choosing 'Number Format', and setting it to a Number or Custom format with zero decimal places. This makes 1E-15 display exactly as 0 without affecting calculations.

Excel & Data Problems