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
Pingback: #Excel Super Links #2 – shared by David Hager | Excel For You
Pingback: KPI y visualizaciones graficas | artesaniadelsoftware.com
Pingback: Excel UNICHAR function is also in Power BI – We are in the world of Self service BI.
Very useful Tipp ! Thanks a lot Chris. I´ll drop by more often now 🙂
Pingback: PASS BA Marathon - Compelling Visuals in Power BI - Prathy's Blog...
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
Pingback: Power BI Desktop/DAX – Using Unicode (Arrows) as Measures in a Table – Gilbert Quevauvilliers – BI blog
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.
Pingback: Generating Random Initials in Power BI by David Hager | Excel For You
Pingback: Power BI Visualizations Follow Up | SQL RICAN
Pingback: Microsoft Data Insights Summit Videos Published – Chris Webb's BI Blog
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.
Pingback: 5 Power BI Tips (2nd edition) - Ziv Himmelfarb
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 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.
Pingback: Creating Animated Reports In Power BI With The Drilldown Player Custom Visual – Chris Webb's BI Blog
Pingback: Obscure MDX Month: Recreating The Star Ratings Measure In MDX Using Excel Functions – Chris Webb's BI Blog
Pingback: DAX UNICHAR Function in Power BI - Excelerator BI
Pingback: Условное форматирование графических объектов в Power BI Desktop — Power of BI