DAX UNICHAR Function in Power BI - Excelerator BI

DAX UNICHAR Function in Power BI

I 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 function is to be able to include non text characters inside visuals.  When you combine a logical set of these codes (icon sets) you can create useful visualisations like the one below.

Note at this time it is not possible to apply conditional colour formatting to a measure that returns a text value (such as the Trend measure above).

Function and Character Overview

The UNICHAR() function accepts an integer that maps to the character you would like to display in your visual.   The easiest way to find the character you want is to consult an online resource such as this one.  https://unicode-table.com/

Here is a sample

Note you can also go to an indexed version of this website here https://unicode-table.com/en/blocks/ which also has a pretty good search feature.  I particular like this set and wonder what could be done with these! https://unicode-table.com/en/blocks/miscellaneous-symbols-and-pictographs/

Uses for UNICHAR Characters

One good use for UNICHAR characters in Power BI visuals is to use them for conditional formatting like in the first image above.  In addition you may be aware it is good practice to avoid relying on colours in visuals that cannot be interpreted by colour blind people.  UNICHAR characters can help here allowing you to use both colour with the numbers and an icon to indicate direction.

Trend Formatting with Icon Sets

The general pattern for using UNICHAR characters in a visual is to do the following:

  • Identify what you want to communicate. eg variations in performance, achievement of target.
  • Find a set of icons that communicate the concept well.

I have chosen the following 3 characters with UNICODE 11165, 11167, and 11166 for this demo.

Next, you need to write a DAX formula (measure) that will return the relevant character using the UNICHAR function.  Note in the example below, I have used the VAR syntax to make the formula easier to read and maintain.

Trend =
    VAR Down = UNICHAR(11167)
    VAR Up = UNICHAR(11165)
    VAR Side = UNICHAR(11166)
    RETURN
       IF([%Chg vs LY] < 0, Down, IF([% Chg vs LY] > 0.1, Up, Side))

When the above measure is added to a table, it looks like the image at top (shown again below).

And with a bit of imagination, you can achieve this:

Here is the modified DAX for the image above

Trend2 =
    VAR Down = UNICHAR(11167)
    VAR Up = UNICHAR(11165)
    VAR UpLots = UP & Up
    VAR Side = UNICHAR(11166)
    Return
       SWITCH(True(),
           [% Chg vs LY] < 0, Down,
           [% Chg vs LY] > 0.2, UpLots,
           [% Chg vs LY] > 0.1,Up,
           Side
       )

KPIs with Icon Sets

In this next example, I have used a set of icons to communicate status vs a KPI using Margin % against a standard target.  The input measures used are:

Total Margin$ = [Total Sales] - [Total Cost]
Margin% = DIVIDE([Total Margin$], [Total Sales])

The next step is to choose the icon set characters that communicate tracking vs target.  In this example The purpose of a KPI is to know if the required target is met or not, or how it is tracking. So, I have chosen the following characters with UNICODE 128504, 33, and 128500.

I wrote the DAX formula for the KPI status using VAR and also the SWITCH True() pattern as above:

Margin% Status =
   VAR AcheivedIcon = UNICHAR(128504)
   VAR AveragePerfIcon = UNICHAR(128500)
   VAR NotAcheivedIcon = UNICHAR(33)
   VAR Target = 0.6
   VAR Warning = 0.4
   RETURN
      SWITCH(TRUE(),
         [Margin%] < Warning, NotAcheivedIcon,
         [Margin%] > Target, AcheivedIcon,
         AveragePerfIcon
       )

The final Table visual looks like this.

Most Useful Characters

There are many characters available that you can access via UNICODE.  Here are a few (I will update it as I find more useful characters).

Character Code Description
! 33 exclamation mark
? 128504 tick mark
? 128500 cross mark
11167 arrow down
11165 arrow up
11166 arrow right
9940 No Entry
10133 Plus sign
10134 Minus sign
10006 Multiplication sign
10135 Division sign
9898 White Circle
11036 White Square
9899 Black Circle
11035 Black Square
? 128200 Chart increasing
? 128201 Chart decreasing
? 128578 Smiling face
? 128577 Frowning face
? 128528 Neutral face
? 128070 Index Pointing Up
? 128073 Index Pointing Right
? 128071 Index Pointing Down
? 128072 Index Pointing Left
? 128077 Thumbs Up
? 128078 Thumbs Down
9996 Victory
9997 Writing Hand
? 128176 Money bag
? 128178 Dollar sign
? 128681 Flag
? 127943 Horse riding
? 128692 Biking
9977 Bouncing ball
? 127947 Lifting weights
? 128675 Rowing boat
? 127940 Surfing
? 127946 Swimming

A Final Word

I would love to hear from you about which character codes you find most useful for your Power BI reporting.  I will then edit the section above in this post as a master list for others to reference.  Note that UNICHAR is not available in Excel as yet.

Edit: Be sure to read Jes’s comments below (Oxenskiold).

29 thoughts on “DAX UNICHAR Function in Power BI”

  1. Great article Matt. I was trying to use this to insert the arrows (1116*) into a table. The only issue in the Aug-18 version those UNICHAR codes seem to give me ‘squares’ like you show in your table above. All the other codes work in my DAX (i tried with the ‘thumb up’ and worked like a charm. Any idea what might cause this?

  2. Sarah Salisbury

    I love this article! I have a question. Since the Trend is a measure, what is the best way to add a visual level filter if I wanted to filter on one of the trend arrows?

    1. Mmmm, good question. Options I can think of include:
      Try the filter pane for the visual on the right. You can apply filters on measures there, but I guess it will show the Unicode char.
      Write another measure that returns just the up or down results and add that as a visual filter.

  3. Great article, thanks Matt. I was disappointed/frustrated to learn that conditional formatting could not be used against card values (maybe a future enhancement for Power BI…) however I’ve used the DAX logic you taught me to generate a Unichar character to produce a couple of measure fields for upward and downward arrows based on the comparison of 2 values in my data i.e. “Daily Arrow DOWN = IF(‘Calendar'[Combined Total Item Cost GBP] >= ‘Calendar'[Daily Despatch Target],””,UNICHAR(9660))” and “Daily Arrow UP = IF(‘Calendar'[Combined Total Item Cost GBP] >= ‘Calendar'[Daily Despatch Target],UNICHAR(9650),””)” Next I’ve colored the font red or green accordingly using visualizations. I’ve then placed these in my dashboard one on top of the other and now when the value is above target the green upward arrow shows and when it’s below target the red downward arrow shows. Pretty pleased with the result! I originally just had the one measure which showed a black up or down arrow but I really wanted to show red and green hence the two measures.

  4. I believe my measures work as shown above. I can’t say what is wrong without seeing what you wrote in the context of the data model. There are lots of things that can go wrong with measures.

  5. Hi Matt, i newbie on power bi, after i used your uni char measure on my table visualization, then error happen and the measure can’t convert to text, is there any solution on this, Many thanks

  6. I was trying to get 128578 Smiling face, 128577 Frowning face and 128528 Neutral face using UNICHAR but when value is displaying it is showing only Neutral face but other two are coming as box. Can you please help

  7. Why is the UNICHAR function not available if I’m using a LIVE connection in PowerBI?

    I can only use it when using a IMPORT connection.

    I building a dashboard that is using a tabular model on SSAS, and I’ve opted to use a LIVE connection.

    I’m still very new to PowerBI, and still trying to play around with what connection to use when.

  8. Hi Matt, this is great, except for me, for some reason it works in Excel (connected to analysis services) but doesn’t in Power BI (same analysis services source).
    Power BI is showing the same icon, which is the icon for the total, but applying that to all the rows. If I replace the UNICHAR characters in the SWITCH TRUE statement with “HIGHER, SAME or LOWER” it works correctly in Power BI.
    Any ideas?

    1. Hi Alexis. I don’t really understand the problem you describe, but it sounds like something wrong with the SWITCH statement. When you hardcode the SWITCH, it works. Is that correct? If so, I suggest you create a test measure to see what is happening with your switch test. Return the actual value to your Matrix instead of the icon and see what is happening. I hope that makes sense.

  9. Hi Matt and all, Just a few lines of consumer information regarding the use of Unicode characters:

    1) Any ordinary Unicode character (as opposed to emoji Unicode characters) you choose will have to exist in the font that power BI uses. In case of Excel any Unicode character will have to exist in the font that the relevant cell(s) is formatted with. This principle applies to browsers as well. That’s why you in some browsers will see a blank square for the missing character instead of the character you expected. The font used (perhaps user configured) doesn’t have this character and falls back to the missing character symbol.
    So if you choose a character and a blank square is shown in Power BI the reason is that the character doesn’t exist in Power BI’s font.

    2) To the best of my knowledge the current version of Unicode doesn’t define a code point (character) with a specific color. It’s up to the operating system (via text services like Rich Edit in windows) or the application to do that. The way this is done is usually that the application or OS intercepts the relevant code point (e.g. 128515 for a smiling emoji) and replaces that character with an icon or bitmap. That icon or bitmap can look completely different from what you see on the Unicode consortium’s web site, it can even be colored/shaped differently from application to application or OS to OS.
    I’m not sure this is implemented in Power BI or Excel. After all those tools are not strictly speaking social media tools.

    Best regards Jes.

    1. Thanks Jes (Oxenskiold) for the comments. I was going a bit mad since I wasn’t able to figure out what the issue was. Are there any recommended testing platforms prior to publishing (in my case to an embedded app)?

  10. Thanks Matt. Good idea to using variables to define what the unicode is referring to

    With so many options available, I still haven’t been able to find coloured traffic signals for Green and Yellow Circles; the decimal unicode for Red circle is 128308. I had no trouble finding the poo emoji though! (It is 128169 if you are curious). As for unichar in Excel, it is available in Excel 2016

    Here is another link https://apps.timwhitlock.info/emoji/tables/unicode

    You could also try generating a list of numbers and adding a calculated column that shows what the corresponding Unicode is. However, depending on the range, it can be a very long list!

    1. Thanks PK. I did originally write a calculated column of all the characters, but it was more than 50,000 rows long and hence wasn’t very practicable. I also noticed there are some colour UNICHAR characters. Finding the good ones is the hard part

  11. What do you mean by A
    “as yet”, Regarding Excel?
    Do you know know or expect it to come to Excel Power Pivot as well ?

    1. No, I don’t know anything other than the DAX language is first developed in Power BI, and I assume that MS doesn’t plan to end up with 2 different products (3 if you include Tabular). I do expect it to come to Excel, but I have no idea when.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top