DAX – How to get a line break in a DAX measure for Labels or Titles in a Power BI report?
Once again, I really enjoy working with Power BI and in the data and analytics space.
I was at a customer and they were drilling through between pages, and I wanted to put in what the filter selections were when they drilled through to the new page.
This would allow them to easily see what had been on the previous page, and to avoid going backwards and forwards.
I quickly created the DAX measure to show the filters, but one thing that bugged me was that it looked a bit clunky and not clear in terms of what the filters were.
I knew that I wanted to use a Line Break to put each filter on a new line. I then remembered that in DAX I can use the UNICHAR character set.
I will use an example below to show what it looked like before and after.
This is what my sample data looked like.
DAX Measure with No Line Breaks
Below is my DAX measure and what it looked like with no line breaks.
Label As Is = VAR SelectedStore = SELECTEDVALUE ( Data[Store], "All Stores" ) VAR SelectedBrand = SELECTEDVALUE ( Data[Brand], "All Brands" ) VAR SelectedItem = SELECTEDVALUE ( Data[Item], "All Items" ) VAR Label = "You selected the following from the store: " & SelectedStore & ", Brand was: " & SelectedBrand & ", Item chose was: " & SelectedItem RETURN Label
A quick explanation from above is that I wanted to get the actual value selected, if there was no selection then default to some text “All ColumnName”
The above resulted in the following DAX measure shown using the card visual
Whilst the above does look ok, it can be a bit difficult to read.
DAX Measure with Line Breaks
I then updated my DAX measure and added in the line breaks.
Label with Line Breaks = VAR SelectedStore = SELECTEDVALUE ( Data[Store], "All Stores" ) VAR SelectedBrand = SELECTEDVALUE ( Data[Brand], "All Brands" ) VAR SelectedItem = SELECTEDVALUE ( Data[Item], "All Items" ) VAR Label = "Store: " & SelectedStore & UNICHAR ( 10 ) & " Brand was: " & SelectedBrand & UNICHAR ( 10 ) & " Item chose was: " & SelectedItem RETURN Label
As shown above what I now did was to put in the UNICHAR ( 10 ) which would then put each item on a new line.
Below is what it looks like, where I also put the details into the Title, which I felt made for easier reading.
One thing I did learn too, was that if the card visual is too small fit in all the text, it will auto-wrap the text to make it fit into the box.
As shown below, it has wrapped the last 2 lines of text
And finally, I thought this same DAX measure could have a use case on other visuals where you do not want the Title to go across but rather down.
The Power BI Feature called Expression-Based Titles, could then be used with the same DAX measure above.
As shown below this is what it would look like.
Whilst the image above might not always be practical it is good to see how flexible Power BI is.
To close off this blog post, I have shown today how to enable the text-based DAX measures to have line breaks which makes for easier reading for report consumers.
Thanks for reading, and as always, all questions and comments are welcome.
You can download a copy of the PBIX here: Titles with Line Breaks.pbix