Pivot Table Issues

Fix Duplicate Excel PivotTable Results with DAX Relationships

Natalie TaylorNatalie Taylor Nov 12, 2026 869 views

Question details

The user needs to prevent duplicate PivotTable results when filters must pass between multiple Power Pivot tables.

Fix Duplicate Excel PivotTable Results with DAX Relationships
Product
Microsoft Excel Power Pivot and DAX
Device & OS
not provided
Scenario
A data model contains related tables, and the user needs one value for each item and type combination in a PivotTable.
Observed behavior
The existing relationship does not propagate the required filter correctly, causing duplicate combinations or repeated PivotTable results.
Before you start

Open the Power Pivot Data Model and verify that the type values match exactly in both tables, including spelling, spaces, and data type. Check the relationship cardinality and filter direction before changing the measure.

Solution 1Recommended

Use TREATAS to create a virtual DAX relationship

Use this method when the existing Power Pivot relationship cannot propagate the type filter from the dimension table to the fact table.

TREATAS applies the selected values from one table as filters to a column in another table. This is useful when a physical relationship is missing, has the wrong direction, or cannot provide the required many-to-one filter flow.

1
Open the measure editor

In Excel, open the Power Pivot window, select the table that contains the measure, and choose Home > Calculations > New Measure.

2
Enter the TREATAS measure

Enter a measure such as: Total Value := CALCULATE(SUM(MoreDetails[Value]), TREATAS(VALUES(Details[Type]), MoreDetails[Type])). Replace the table and column names with the names in your data model.

3
Check the model relationship

In Power Pivot, select Design > Manage Relationships and confirm that the related columns use matching values, the correct cardinality, and an appropriate cross-filter direction.

4
Refresh and test the PivotTable

Return to Excel, select the PivotTable, choose PivotTable Analyze > Refresh, and verify that each item and type now returns a single correctly filtered value.

Avoid mismatched filter values: TREATAS only matches equivalent values. Remove leading or trailing spaces and standardize text or numeric data types if duplicate results remain.
Free Microsoft Office alternative

Use WPS Office for compatible PivotTable spreadsheet workflows

WPS Office is a free, lightweight Microsoft Office alternative for creating, editing, and reviewing compatible spreadsheet files. It offers a familiar interface and supports seamless migration for common Excel workbooks, although Microsoft Power Pivot DAX relationships must still be handled in Excel.

  1. 1. Open a compatible workbook: Launch WPS Spreadsheet and select Open from the Home screen to load an existing .xlsx or .xls workbook.
  2. 2. Review the PivotTable report: Select the worksheet containing the PivotTable and inspect its fields, filters, and displayed values.
  3. 3. Edit and save the workbook: Make supported spreadsheet changes, then choose Menu > Save As and select an Excel-compatible .xlsx format.
Free and lightweight spreadsheet editor for everyday workbook tasksFamiliar interface that helps Excel users migrate with minimal adjustmentSupports Microsoft Excel .xlsx and .xls formats for easier file sharingUseful for reviewing PivotTable reports and editing compatible spreadsheet data
microsoft office alternative - wps office

Frequently Asked Questions

Why does my Excel PivotTable show duplicate results after I create relationships?

The relationship may not propagate filters in the required direction, or the related columns may contain duplicate, mismatched, or inconsistent values. Check cardinality, filter direction, and the exact values in both tables.

What does TREATAS do in a Power Pivot measure?

TREATAS creates a virtual relationship by applying values from one column as filters to another column. In this case, VALUES(Details[Type]) filters MoreDetails[Type] inside CALCULATE.

Where can I create the DAX measure in Excel?

Open the Power Pivot window, select the destination table, and choose Home > Calculations > New Measure. You can also create measures from the PivotTable Fields pane when the workbook uses a Data Model.

What should I check if TREATAS still returns duplicates?

Confirm that the two type columns contain matching values and compatible data types, remove extra spaces, and check whether the item dimension is unique. Also refresh the Data Model and PivotTable after updating the measure.