How to Use a DAX Measure to Concatenate Distinct Dates in an Excel Power Pivot PivotTable
Question details
The user needs a DAX measure that extracts distinct dates for an ID and displays the concatenated dates in the Values area of a Power Pivot or Excel PivotTable.
- Product
- Microsoft Excel Power Pivot
- Device & OS
- not provided
- Scenario
- Building a PivotTable that shows all distinct dates linked to an ID in a single cell in the Values area.
- Observed behavior
- The target state is a DAX measure that returns distinct dates as concatenated text in the Values area instead of the default numeric aggregation.
Confirm that the date column uses a Date or DateTime format and that the table is loaded into the Power Pivot data model. Also verify that DAX measures are available in your Excel version.
Use CONCATENATEX with DISTINCT to Concatenate Distinct Dates in a DAX Measure
Use this measure when you want each ID to show a space-separated list of its distinct dates in the Values area of a Power Pivot PivotTable.
CONCATENATEX iterates over the distinct date values and joins them with the specified delimiter. Placing the measure in the Values area displays the resulting text for the current PivotTable filter context.
In Excel, go to the Power Pivot tab and click Manage to open the data model window.
Select the target table, then click Measures > New Measure in the Power Pivot window.
In the Measure dialog box, set Measure name to DistinctDates and enter: DistinctDates := CONCATENATEX(DISTINCT(Table1[Date]), Table1[Date], " ").
Return to the Excel PivotTable and drag the DistinctDates measure into the Values area to display the concatenated dates.
Count Distinct Dates with a COUNTROWS and DISTINCT DAX Measure
Use this measure when you only need the number of distinct dates per ID instead of a concatenated text list.
Use WPS Office as a Free, Lightweight Alternative for PivotTable Work Without DAX
WPS Office is a free, lightweight Office alternative that opens, edits, and saves Excel workbooks with a familiar PivotTable interface and Excel format compatibility. It does not run Power Pivot DAX measures, but supports common pivot table summaries and smooth migration from Excel.
- 1. Download WPS Office: Click Download WPS Office Free, run the installer, and complete the setup on your computer.
- 2. Open the Excel workbook: Launch WPS Spreadsheets, click Menu > Open, and select the .xlsx workbook you want to summarize.
- 3. Create a PivotTable summary: Select the data range, go to the Insert tab, click PivotTable, and use the field list to summarize dates by ID without DAX.
Frequently Asked Questions
Can I use this DAX measure in WPS Spreadsheets?
WPS Spreadsheets does not support Power Pivot DAX measures. You can recreate common PivotTable summaries with WPS PivotTable fields and formulas, or open the workbook in Microsoft Excel when DAX is required.
Why does my CONCATENATEX measure show dates as numbers instead of formatted dates?
Power Pivot stores dates as numbers internally. Use FORMAT(Table1[Date], "yyyy-mm-dd") inside CONCATENATEX to control the date format returned by the measure.
Can I concatenate distinct dates only for the selected ID?
Yes. The measure responds to the PivotTable filter context, so row labels, slicers, and filters for the ID automatically limit the distinct dates included.
How do I count distinct dates instead of listing them?
Create a measure with COUNTROWS(DISTINCT(Table1[Date])) and place it in the Values area to return a numeric distinct date count for each ID.
Does this work in a regular Excel PivotTable without Power Pivot?
No. DAX measures require the Power Pivot data model. In a normal PivotTable, add the source data to the Data Model when creating the PivotTable or use helper columns for distinct date summaries.
