Using ConcatenateX with a "New line" as delimiter...

[MHjortkjaer]

Hi all,
Is it possible to insert at new line (Alt-enter / <br> / etc) as a delimiter in a ConcatenateX formula (Measure). I Power BI the formula would be Unichar(xx), but that does not seem to exist in Excel... Any suggestions?

 

[jasonb75]

Welcome to Mr Excel

I'm not sure about ConcatenateX (never used it) but in a regular formula you would use CHAR(10) in conjunction with 'Wrap Text' formatting.

Hopefully this will do what you need.

 

[MHjortkjaer]

Thanks - yeah - but i DAX the Formula Char is not known... Thank you for trying...

Welcome to Mr Excel

I'm not sure about ConcatenateX (never used it) but in a regular formula you would use CHAR(10) in conjunction with 'Wrap Text' formatting.

Hopefully this will do what you need.

 

[jasonb75]

See if this helps, DAX – How to get a line break in a DAX measure for Labels or Titles in a Power BI report? - Reporting/Analytics Made easy with FourMoo and Power BI

 

[MHjortkjaer]

Hi Jason

Thaks for the link. Unfortunately the UNICHAR formula does not exist in the Excel variant of DAX. But thanks for trying.
/Mads

See if this helps, DAX – How to get a line break in a DAX measure for Labels or Titles in a Power BI report? - Reporting/Analytics Made easy with FourMoo and Power BI

 

[jasonb75]

I think that you might be out of luck then. Other suggestions that I found mentioned Shift Enter while typing the formula, but I can't see that working.

 

[KellerAg92]

I was trying to do the same thing and just found an easy answer. Basically, put the ending portion of the CONCATENATEX argument in the define Measure for the Pivot Table on a second line by typing a return. For example, put a quotation mark on one line and second quotation mark on the second line before closing the formula with the parentheses.

I found this at the following linke: 5 Ways to Concatenate Data with a Line Break in Excel | How To Excel.

It is also possible to make things a big fancier and add a special character to start each line, roughly simulating bulleted text.

 

[MHjortkjaer]

Hi KellerAg92.

Thank you - nice and simple...

Did you have succes on actually doing af bullet-list of the items - when copied to ie Powerpoint will actually result in a bullet-list in a single cell?

/Mads

 

[KellerAg92]

Hi KellerAg92.

Thank you - nice and simple...

Did you have succes on actually doing af bullet-list of the items - when copied to ie Powerpoint will actually result in a bullet-list in a single cell?

/Mads

I have not tried to take anything out of Excel over the PowerPoint, but I had the same idea. Staying in Excel and in a Pivot table, I added a minus sign ("-") in the concatenate sequence and it roughly serves as a "bullet." I have not been able to find how to force an actual bullet character in (CHAR and UNICODE don't seem to work in DAX). There might be a keyboard sequence to type a bullet ASCII character.

Beyond the listing, I have also been able to add sums of values associated with the text strings (e.g., total sales across the items listed in a cell consistent with the row/column segmentation).