How to Fix Excel Data Model Many-to-Many Relationships in PivotTables with DAX
Question details
The user needs a DAX measure that displays related descriptions for a selected Type, excludes blank counts, and works around unsupported many-to-many relationships in the Excel Data Model.
- Product
- Microsoft Excel Data Model / Power Pivot
- Device & OS
- not provided
- Scenario
- The user is building or modifying a PivotTable based on Excel Data Model tables and needs filtered text descriptions when a Type is selected.
- Observed behavior
- The Excel Data Model does not directly support many-to-many or one-to-one relationships in every scenario, and a standard measure returns blanks or includes blank counts.
Open the workbook that contains the Data Model and confirm that the related tables have Type and Description columns. From Power Pivot > Manage Data Model, review the existing relationships so you can identify which table is the many side before editing the measure.
Create a DAX measure with TREATAS and ISFILTERED to return the selected Type description
Use this when you need a PivotTable row, cell, or card to show the related description for the currently selected Type and suppress blank counts.
This measure uses TREATAS to transfer the Type filter context from Table3 to Table2, and uses ISFILTERED with a non-blank Count check so the description appears only when a Type is explicitly filtered.
In Excel, go to Power Pivot > Manage Data Model, or select Data > Manage Data Model, and open the table where you want to store the measure.
Click an empty calculation area cell in the table and enter: DV := IF(ISFILTERED(Table3[Type]) && [Count] <> BLANK(), CALCULATE(CONCATENATEX(Table3,Table3[Description]),TREATAS(VALUES(Table3[Type]),Table2[Type])),BLANK()). Rename Table2 and Table3 to your descriptive table names.
Return to Excel, select the PivotTable, open PivotTable Fields, and add the DV measure to the Values or Rows area as needed.
Apply a slicer or filter on the Type field. The measure should display the concatenated descriptions only when Type is filtered and the Count is not blank.
Hide many-side fields and simplify relationships in the Excel Data Model
Use this when the many-to-many relationship is not essential for the current PivotTable and you want to avoid duplicated or blank entries from the many side.
Try WPS Office for everyday PivotTables without Excel Data Model complexity
If you only need pivot summaries, Excel-compatible reports, and familiar spreadsheet tools, WPS Office is a free and lightweight alternative that opens .xlsx workbooks and keeps formatting, formulas, and PivotTable basics without requiring Power Pivot or DAX relationship work.
- 1. Download WPS Office Free: Go to the official WPS Office website, download the installer for Windows or Mac, and launch the suite after installation.
- 2. Open your Excel workbook: In WPS Office, click Menu > Open and select the .xlsx file that contains your PivotTable or source data.
- 3. Create or refresh a PivotTable: Select your data range, click Insert > PivotTable, and build the summary you need without configuring an Excel Data Model relationship.
Frequently Asked Questions
Why does my DAX measure show a blank even when a Type is selected?
The measure probably returns BLANK because Count is blank or the filter context is not transferred to the related table. Use ISFILTERED(Table3[Type]) and check that [Count] <> BLANK(), then wrap the calculation with TREATAS on the Type values.
How do I create a virtual relationship with TREATAS in the Excel Data Model?
In Power Pivot, add a measure that uses TREATAS(VALUES(Table3[Type]), Table2[Type]) inside CALCULATE. This transfers the Type filter from Table3 to Table2 without creating a physical many-to-many relationship.
Can I use many-to-many relationships directly in an Excel PivotTable?
The Excel Data Model does not directly support many-to-many or one-to-one relationships in every scenario. The usual workaround is to hide fields from the many side, restructure the tables, or use a DAX measure with TREATAS.
Why should I rename Table1 and Table2 in the Data Model?
Descriptive table names make DAX measures and relationships easier to read and maintain. They also avoid ambiguous column references when you use TREATAS, CALCULATE, or CONCATENATEX.
What if I do not need DAX and just need a simple pivot summary?
You can open the workbook in WPS Office Spreadsheets and create a regular PivotTable from the source data. This avoids Data Model relationship setup and works for many basic reporting tasks.
