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 DAX UNICHAR Characters
One good use for DAX 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 DAX 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 for DAX UNICHAR
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). Edit: One of my past students, Ruth, posted below that many of these characters are not iPad friendly, so watch out for that if needed.
|?||128070||Index Pointing Up|
|?||128073||Index Pointing Right|
|?||128071||Index Pointing Down|
|?||128072||Index Pointing Left|
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).