The UNICHAR() DAX function is a text function that takes a numerical Unicode value and displays its associated character. For example, UNICHAR(128515) will display as:
90% of the information the human brain processes is visual and we process images up to 60,000 times faster than text, so it makes perfect sense to use icons where possible to enhance reports. This scarcely used DAX function opens-up that option.
The below stacked column chart uses Unicode emoticons to enhance the readability of the ‘Genre’ axis labels.
So, how do we achieve this?
To produce this you will need to edit the query. In the ‘Data’ view, right click the relevant table and select “Edit Query”
First, duplicate the existing column you want Unicode characters for (genre in this case). Then use the ‘Replace Values’ option to substitute in the relevant Unicode numbers for each genre.
(this can be hidden from the report view as it contains nothing meaningful).
Next, create a second calculated column that uses a simple measure:
IconColumn = (UNICHAR(UnicodeNumberColumn))
This new ‘Icon’ column can now be used in reports the same way as any other text column.
Note how in the stacked column chart above, the original names have been included, this is good practice for two main reasons. One is clarity, a clown denotes comedy to most users, but could indicate horror to others, including the label removes the ambiguity.
The other reason is due to possible compatibility issues. It is worth pointing out here that the Unicode characters will only display when the character exists in the chosen font. In most cases this will be fine, especially for emoji characters, but just in case there are display issues it is worth including the full label.
Staying with the movie topic, the below chart shows movie ratings both numerically and visually created by a custom measure:
Stars = REPT(UNICHAR(11088), AVERAGE('IMDB 1000'[10 Star Rating]))
A measure that uses the UNICHAR() function will always be a text field and as such, normal formatting applies, in the example above we can set colours to be gold on a black background.
The previous examples do help readability but don’t really add anything meaningful to the report. The below table shows that the UNICHAR() function can add worthwhile content with customisable KPIs by combining it with conditional formatting.
There are 143,859 Unicode characters available, everything from emojis, symbols, shapes and braille patterns to dice and playing cards. Whether you want to offer further insight into your data, enhance the user experience or simply create something sublimely ridiculous, with so many icons at your fingertips, the possibilities are only limited by your imagination.
Further information on the UNICHAR() function can be found here: UNICHAR function (DAX) – DAX | Microsoft Docs
A list of Unicode characters and their respective numerical values can be found here: Huge List of Unicode Characters
Which date format styles should we use if we are building a report that is being consumed internationally?
Remember, 01/12/2021 is December 1st or January 12th depending in which part of the world it is being read.
The decision may be taken from our hands if there is a company policy in place. If the company is based in the USA, for example, they may choose to use US formatted date fields as a standard for reporting across the entire business, however, if the field needs to be truly dynamic depending on the consumers location, the answer lies in this tool tip:
There are 2 formats in the selection that are prefixed with an asterisk:
There are 2 variables that the Power BI Service checks when loading reports in the service.
First it will check the language setting of the user account in the service. This is set under ‘Settings >> General >> Language’. There is a dropdown option that acts as both a language and regional setting, this drives how dates are formatted when dynamic date formats are used.
If this is set to ‘Default (browser language)’ the second variable, the browser’s default language setting, will take effect.
In Edge this is set under ‘Settings >> Language’, when multiple languages are set, the topmost one is considered the default.
In Chrome it is set under ‘Settings >> Advanced >> Language’, this uses the same system as Edge where the topmost language is used as default.
Here is an example of a table loaded in a browser using both English UK and English US:
This example shows that not only does the format of the date itself change (day and month have switched) but there are also visual connotations to account for. The US format uses a 12-hour clock by default and the addition of the AM/PM suffix changes the column width and drastically alters the readability of the table and potentially the entire report. It is these occurrences we need to be aware of when developing reports for international consumption.
This issue can easily be avoided by using the ‘Auto-size column width’ setting under ‘Column Headers’ on the formatting tab of the visual, or by allowing for the growth when setting manual column widths. (For a great guide on manually setting equal column widths, please read this helpful post by my colleague, Nick Edwards)
Unfortunately, this post comes with a caveat, at the time of writing it would seem there is a bug in Power BI. Remember this from earlier?
As you can see below, both fields use the UK format of DD/MM/YYYY when the browser language is set to English UK.
However, when the browser settings are changed to English US, only the *‘General Date’ format has changed, the *’DD/MM/YYYY’ format is still showing in the UK format even though there is an asterisk next to it in the selection list.
Hopefully once this issue is addressed, the use of regionally dynamic date formats will be available for both long and short formats.
A drill through in Power BI allows the reader to see secondary data related to the original page with the context of a specific data point applied, for example, drilling through on sales data can display the demographic information of the relevant customers for those sales.
One limitation of the drill through functionality is that it only allows users to drill through on a single data point. If more than one is selected, the drill through function will be disabled. Using the above example, this means that a reader can drill through to the demographic of the sales of one product at a time, but not a combination of two or three.
You can see this when using a drill through button, the button only works when one data point is selected.
If you select multiple points the button is greyed out and if you hover over the it, you get the following tool tip appear:
“To drill through to [page name], select a single data point from [page name]“
Curiously, since native drill throughs on card visualisations were introduced back in September 2020, Power BI considers a card to be a single data point, regardless of the number of filters applied to it.
If you drill through on the card with multiple data points selected, the drill through page will have all of the relevant filters applied.
Currently there is no method of getting the button to function with multiple data points selected, even though the above behaviour suggests there is scope to do so. At the time of writing, Microsoft have confirmed that this behaviour is intended functionality for the drill through button.
So to conclude, if you need to allow drill throughs for a multi-select scenario, currently your only option at the moment is to replace Buttons for Cards and perhaps include a tip for the reader to know its there, hopefully this may change in the future.