How to Create a Bulleted List with Line Breaks in Power Pivot Measures
Question details
The user wants to display multiple items from a DAX CONCATENATEX measure as a bulleted list with line breaks within a single Excel PivotTable cell.
- Product
- Excel / Power Pivot
- Device & OS
- not provided
- Scenario
- Writing a DAX measure to combine multiple text items and displaying them neatly in a Pivot Table report.
- Observed behavior
- The measure currently outputs items separated by bullets but keeps them all on a single line instead of stacking them with line breaks.
Ensure that the 'Wrap Text' formatting is enabled for the cells in your PivotTable where the measure will be displayed, otherwise the line breaks will not be visible.
Use UNICHAR(10) in the CONCATENATEX Formula
Modify your DAX formula to include a bullet symbol and use UNICHAR(10) as the delimiter to force a line break between concatenated items.
The DAX function UNICHAR(10) inserts a line feed character, which Excel interprets as an in-cell line break. By appending a bullet character to the item expression and setting UNICHAR(10) as the delimiter, the PivotTable will render each item on a new line.
Open the Power Pivot window or the Measure creation dialog in Excel, and select the DAX measure you want to edit.
Update your formula to structure the bullet and line break. For example: =CONCATENATEX(Goals, "• " & [Item], UNICHAR(10)).
Save the measure and ensure it is added to the Values area of your PivotTable.
Select the PivotTable cells containing the measure, navigate to the Home tab on the ribbon, and click 'Wrap Text' to make the line breaks appear.
Try WPS Office for Your Data Analysis and Spreadsheets
If you are dealing with complex PivotTables and need a reliable, lightweight spreadsheet tool, consider WPS Office. It provides powerful data analysis features, familiar interfaces, and excellent compatibility with Excel formats without the heavy subscription costs.
Frequently Asked Questions
Why aren't the line breaks showing up in my PivotTable?
Even if your DAX measure includes UNICHAR(10), Excel will visually ignore the line breaks until you enable 'Wrap Text' for those specific cells. Select the cells and click 'Wrap Text' on the Home tab.
Can I use a different bullet character in my DAX formula?
Yes. You can insert any unicode character or symbol into the expression string. For example, you can use a dash ("- ") or use the UNICHAR() function with a different character code to generate custom bullet points.
Does CONCATENATEX support sorting the bulleted items?
Yes, the CONCATENATEX function allows you to specify an orderBy_expression and an order (ASC or DESC) as optional arguments after the delimiter, so your bulleted list can be sorted alphabetically or numerically.
