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

[…] https://blog.crossjoin.co.uk/2017/04/11/the-dax-unichar-function-and-how-to-use-it-in-measures-for-d… […]

[…] The DAX Unichar() Function […]

[…] The DAX Unichar() Function And How To Use It In Measures For Data Visualisation […]

Very useful Tipp ! Thanks a lot Chris. I´ll drop by more often now 🙂

[…] first saw this technique when Will Thomson showed at UK Power BI Summit also recently Chris Webb blogged about using Unichar() function. In my demo, I just copied Emoji’s from the Web and placed them […]

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

[…] in order to get the Unicode Characters I followed the very interesting blog post by Chris Webb: The DAX Unichar() Function And How To Use It In Measures For Data Visualisation, in which I learnt how to use Unicode Characters in a […]

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.

[…] https://blog.crossjoin.co.uk/2017/04/11/the-dax-unichar-function-and-how-to-use-it-in-measures-for-d… […]

[…] – Blog for using UNICHAR in Power BI here […]

[…] star rating measure from this blog post has been turned into a Quick Measure! My name is in Power BI! I am so chuffed! The important thing […]

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.

[…] Example 1 […]

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.

[…] add it to the table in the report. This measure uses my old favourite the Unichar() function to display an arrow against the row in the table that matches the currently selected hour […]

[…] start off with recreating my ever-popular DAX star-ratings measure in MDX. Well, not exactly pure MDX, but did you know that in MDX you can call some Excel functions […]

[…] first learnt about the new DAX UNICHAR function in Power BI by reading Chris Webb’s blog (which was also the inspiration for this article today). One of the uses of the UNICHAR […]