A few weeks ago I was asked whether it was possible to display line breaks in text in a Power BI visualisation. It turns out it isn’t possible – at the moment Power BI always strips line breaks out of text when it gets loaded into the Data Model. However while researching this I came across the DAX Unichar() function, which returns the unicode character associated with an integer value – and which also seems to be completely undocumented for some reason, I guess because it’s new (it isn’t in Excel 2016 DAX yet as far as I can see).
It’s very straightforward to use: for example, the DAX expression UNICHAR(65) returns the character A; see here for a list of unicode characters and their associated codes. You can have a lot of fun with this function in Power BI when you use it to return symbols that in turn can be used to represent data, so I thought I would put together a few examples to show you.
Take the following table which contains scores for restaurants in the range of 0 to 5:
The following measure:
Stars =
REPT(UNICHAR(9733), AVERAGE('Restaurants'[Score]))
&
REPT(UNICHAR(9734), 5-AVERAGE('Restaurants'[Score]))…which uses the Unichar() function to return characters 9733 and 9734, filled and unfilled stars, and the Rept() function to return a string with those characters repeated N times, can be used to create a table like this in Power BI:
[I’m sure I read a blog post somewhere that describes this trick with Rept() but I can’t find it anywhere – if you know the one I’m talking about please leave a link in the comments]
Similarly, with the following source data showing the days that customers made a purchase in a week:
…you can use the following measure, which returns characters 9635 (a square with a black dot inside) and 9634 (an empty black square), in a matrix to visualise this information:
Purchase Indicator =
IF(
COUNTROWS('Purchase Days')>0,
UNICHAR(9635),
UNICHAR(9634)
)
& REPT(" ", 5)
Finally, an example using the box drawing unicode block to visualise the following date ranges (nb the dates are in dd/mm/yyyy format). Here’s the source data:
Here’s the measure, which uses characters 9500, 9472 and 9508 to draw bars:
Employment Range =
VAR OverallMinimumDate =
CALCULATE(
MIN('Employment Dates'[Start Date]),
ALLSELECTED('Employment Dates'))
VAR DaysBeforeStart =
MAX('Employment Dates'[Start Date]) -
OverallMinimumDate
VAR DaysBetweenStartAndEnd =
MAX('Employment Dates'[End Date]) -
MAX('Employment Dates'[Start Date])
VAR BarsBetween =
IF(DaysBetweenStartAndEnd>1,
DaysBetweenStartAndEnd-2,
0)
RETURN
REPT(" ", DaysBeforeStart) &
UNICHAR(9500) &
REPT(UNICHAR(9472), BarsBetween) &
UNICHAR(9508)And here’s the output in a table:
You can download the Power BI .pbix file with these examples in here.
Is this going to revolutionise how you design reports? No of course not, but I think it could be a useful trick in certain scenarios. If you do come up with other creative ways to use unicode characters in your reports I would be interested to see the results!
really cool!!! 🙂
Awesome, Chris. Pure awesomeness! 🙂
First, this is awesome. Second, I couldn’t help myself. This is the first thing I thought of when I saw this. (ノ͡° ͜ʖ ͡°)ノ︵┻┻ – you can reproduce this with this measure 🙂 TableFlip = UNICHAR(040) & “” & UNICHAR(12494) & “” & UNICHAR(176) & “” & UNICHAR(662) & “” & UNICHAR(176) & “” & UNICHAR(41) & “” & UNICHAR(12494) & “” & UNICHAR(65077) & “” & UNICHAR(9531) & “” & UNICHAR(9531)
Thank You !!
Just implemented the same logic in Excel.. Looks fine trick..
Cool trick, Chris!
Got to love these undocumented gems. Thanks for sharing 🙂
Reblogged this on George's BI Blog and commented:
Great! One of my favorite feature in Tableau is now available in Power BI.
That’s really nifty Chris. You can create some very creative reports this way. People should just remember that the selected unicode characters will have to be present in the font they are using in the report. As far as I know Power BI doesn’t support composite fonts.
❤❤❤🖤🖤
💛💛💛💛🖤
💙💙💙💙💙
This tips can use with emoji.
Thanks
How did you colorize the Emoji in Power BI desktop? is it possible?
You can set the font colour (and alternate font colour) for rows in a table, but that only works for the whole row I think.
Chris.. what can I say? Keep doing what you doing, awesome post.
Good one
Very useful Tipp ! Thanks a lot Chris. I´ll drop by more often now 🙂
Mynda Treacy has referred to rept() function in her blog titled “Excel Factor Entry 3 Re-format Data Using Formulas” with following link:
http://www.myonlinetraininghub.com/excel-factor-entry-3-re-format-data-using-formulas#sandy
MS also has it’s documentation below:
https://support.office.com/en-us/article/REPT-function-04C4D778-E712-43B4-9C15-D656582BB061?ui=en-US&rs=en-US&ad=US
How do I resolve this error:
Failed to resolve name ‘UNICHAR’. It is not a valid table, variable, or function name.
The Unichar() function is quite new – it may not be available in older versions of Power BI Desktop or Excel.
Chris, I stumbled on this today and it is still not available in Excel (1904 Build 11601.20204) – after two years. Interestingly the reverse function UNICODE() is available.
When I go to the Unicode Wikipedia link, I see codes like “U+25A3” for the square with the black dot inside (for example). Where do you see 9635? Letters can’t be used in UNICHAR(). Is there some kind of conversion that you have to do to get to the numerical code? Thank you.
When I go to the Unicode Wikipedia link, I see codes like “U+25A3” for the box with the dot in the middle (for example). Where do you see 9635? Letters are not allowed in the UNICHAR() function. Is there some kind of numerical conversion you have to do? Thank you.
In Wikipedia the Code column in the table gives you the unicode code in hexadecimal. You need to convert this to a decimal number to work with the Unichar() function: 25A3 in hexadecimal is 9635 in decimal.
Hi, thanks for your post Chris.
I have a problem. I can’t use UNICHAR() with live SSAS connection. If i use Excel data or “import query from SSAS” it works like a charm.
How to fix it with a live connection to Tabular model?
Thanks
Unichar() is a new function, so I guess it will only work with the most recent versions of SSAS Tabular (ie the 2017 CTP or Azure SSAS).
Hi Chris,
Is there an alternate to UNICHAR while using SSAS 2016 with Power BI. We do not have liberty to upgrade the version. But user wants to see some symbol on the cards.
Hi when we have the Avgerage in decimal the start is not filled partially. is there a way to overcome this?
Hi when we have the Avgerage in decimal the star is not filled partially. is there a way to overcome this?
Since there is no unicode character for a half-filled star (that I know of), you can’t get around this problem. Sorry!
You might try 1F31F (127775) as a stopgap.
Thanks for this trick. It is quite useful for our reports. Do you know why sometimes when you copy and paste into EXCEL from another app (browser or another MS program), the unicode 8203 is carried over? This single issue has delayed my project for hrs. Thanks!
But still UNICHAR(10) is not working for the card visualizations. Others stars and all that are working. Any solutions for Line breaks
Thank you for this! I was able to use it successfully for my dashboards, with the exception of one tile where the average value was 4.5, but it keeps showing as 5 out of 6 stars. I used the exact same formula as with all my other tiles, which is the same as shown here. Any thoughts on why this is happening? Thanks.
try this:
Stars =
var decAR = AVERAGE(‘Ratings'[Rating])
var decRAR = rounddown(AVERAGE(‘Ratings'[Rating]),0)
var fs = UNICHAR(9733)
//var hs = UNICHAR(11240)
var ns = UNICHAR(9734)
return
if(
decAR > decRAR,
REPT(fs, decRAR)
&
REPT(ns, 5-decRAR)
,
if(
decAR = decRAR,
REPT(fs, decRAR)
&
REPT(ns, 5-decRAR)
,
BLANK()
)
)
great job! have you heard of any updates, or workarounds for implementing half stars? In a table visual, the only way I was able to get partial stars was through the use of images, and with REPT function. Would love to simplify what I have with this if I could get half stars.
How can create an empty star if the particular row has no rating or 0 stars?
How can I show the 0 ratings?
can you reply me please