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

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:

image

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:

image

[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:

image

…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)

image

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:

image

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:

image

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!

38 thoughts on “The DAX Unichar() Function And How To Use It In Measures For Data Visualisation

  1. 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)

  2. 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.

  3. How do I resolve this error:
    Failed to resolve name ‘UNICHAR’. It is not a valid table, variable, or function name.

  4. 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.

  5. 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.

  6. 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

Leave a Reply to Generating Random Initials in Power BI by David Hager | Excel For You Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s