How to Use Unicode Characters in Power BI

A common dashboard requirement is to take a set of text-based values and visually represent them as colorful icons in Table or Card visualizations. With tools like Microsoft Power BI or TIBCO Spotfire, it is fairly simple to map specific data values or data ranges to a set of default icons through conditional formatting. However, if you want to display an icon in a Card visualization in Power BI, then it’s a slightly more complicated process using a combination of DAX (Data Analysis Expressions) measures and Unicode characters to produce the expected result.

The following image illustrates the expected result using two different methods. The “STATUS” column uses the default icons available when conditionally formatting a column in a Table visualization. The “MEASURE STATUS ICON” column displays the Unicode character output from a DAX measure which is then colored using another DAX measure and conditional formatting. I find the DAX method to be more useful since it may also be used to format other visualizations such as the Card visualization as shown in the second image.

I’ll demonstrate both methods, but the DAX method is the primary focus of this example.

Step 1 – Create Underlying Data

To demonstrate this functionality, I have a list of Project Names and their associated Status listed in an Excel worksheet. The set of valid Status values include “Not Started”, “On Track”, “At Risk”, “Off Track”, and “Complete”.

Step 2 – Import Data

Starting with a new report in Power BI, the Excel file containing the Project Name and Status list worksheet is imported. Click Import data from Excel and select the appropriate file.

Step 3 – Transform Data

As part of the import process, the data may need to be transformed if the column headings are not recognized. In the screenshot below, the columns are labeled “Column1” and “Column2” instead of “PROJECT NAME” and “STATUS”. Click Transform Data.

The Power Query editor opens. As part of the Applied Steps, click Use First Row as Headers on the File ribbon to promote the first row as the new column headings.

Step 4 – Convert Text Values to Numbers

In this step, the text-based Status values are mapped to numeric values. This mapping eases both the conditional formatting configuration as well as the DAX measures which are created in later steps. Also, from a maintenance perspective, it reduces the number of locations where hard coded text values need to be modified when someone eventually asks to rename the values.

Switch to the Data view and then click New column on the Table tools ribbon.

Using a Switch function, the text-based Status values are mapped to numeric values. I chose increments of 100, but you may choose any unique numeric integers. The new column is named “STATUS NUMERIC”.

• Not Started = 100
• On Track = 200
• At Risk = 300
• Off Track = 400
• Complete = 500

STATUS NUMERIC = SWITCH([STATUS],"Not Started", 100, "On Track", 200, "At Risk", 300, "Off Track", 400, "Complete", 500)

Step 5 – Create DAX Measures

Now that we have numeric values mapped in the “STATUS NUMERIC” column, those values can be used by two new DAX measures to map to Unicode characters as well as hex color codes.

On the Home ribbon, click New measure and add the following code to create the icon measure. Repeat for the process to create the icon color measure.

The numbers used in the UNICHAR function correspond to the equivalent HTML codes for a Figure Dash (8210), Black Large Circle (11044), Black Up-Pointing Triangle (9650), Black Diamond (9670), and White Circle (9675).

MEASURE STATUS ICON = 
    VAR vStatusNumeric = SELECTEDVALUE('PROJECT DATA'[STATUS NUMERIC])
    RETURN
        SWITCH(
            TRUE(),
            vStatusNumeric = 100, UNICHAR(8210),
            vStatusNumeric = 200, UNICHAR(11044),
            vStatusNumeric = 300, UNICHAR(9650),
            vStatusNumeric = 400, UNICHAR(9670),
            vStatusNumeric = 500, UNICHAR(9675)
        )

In simplified terms, the hex color codes correspond to black, green, yellow, and red (although these are not the equivalent of the HTML color names green, yellow, and red). I have also excluded the 500 value since the corresponding Unicode character is, by default, in the color I expect.

MEASURE STATUS ICON COLOR = 
    VAR vStatusNumeric = SELECTEDVALUE('PROJECT DATA'[STATUS NUMERIC])
    RETURN
        SWITCH(
            TRUE(),
            vStatusNumeric = 100, "#000000",
            vStatusNumeric = 200, "#84C28A",
            vStatusNumeric = 300, "#F9D087",
            vStatusNumeric = 400, "#F78272"
        )

Step 6 – Create Table Visualization

Switch to the Report view and add a Table visualization. Add the “PROJECT NAME”, “STATUS”, and “MEASURE STATUS ICON” fields as Values in the Table visualization. In subsequent steps, we’ll add default icons to the “STATUS” column using basic conditional formatting and color to the “MEASURE STATUS ICON” using a DAX measure. As illustrated in the screenshot, the DAX measure mapping numeric status to Unicode character is working.

Step 7 – Conditional Formatting for Default Icons in Table Visualization

Click on the “STATUS” field in the list of Values for the Table visualization. Select Conditional formatting and then Icons. Follow the screenshot below to configure the formatting of the “STATUS” field using the numeric values defined earlier. This configuration formats the column to show icons immediately to the left of the text values in the STATUS column in the Table visualization.

After applying conditional formatting, the Table visualization now displays icons in the STATUS column.

Step 8 – Conditional Formatting for Unicode Characters in Table Visualization Using DAX Measures

In this step, we’ll add color to the Unicode character icons. Click on the “MEASURE STATUS ICON” field in the list of Values for the Table visualization. Select Conditional formatting and then Font color. Follow the screenshot below to configure the formatting of the “MEASURE STATUS ICON” field using the DAX measure that maps numeric status to a defined color.

Now the Table visualization is fully formatted, as expected, using both methods.

Step 9 – Create Card Visualization

While in Report view, add a Card visualization. Add the “MEASURE STATUS ICON” field to the visualization Values. In subsequent steps, we’ll add default icons to the “STATUS” column using basic conditional formatting and color to the “MEASURE STATUS ICON” using a DAX measure. As illustrated in the screenshot, the DAX measure which maps numeric status values to Unicode characters is working.

Step 10 – Conditional Formatting for Unicode Characters in Card Visualization Using DAX Measures

Click on the added Card visualization and go to its Format options. Find the options for Data label and click the fx button for Color. Configure the options as illustrated below.

Result

Now that everything is configured, both the Table and Card visualizations are working as expected. The Unicode character icons are displayed and colored in both visualizations. If a specific entry in the Table visualization is selected, then the Card visualization displays the corresponding Unicode character with the appropriate color.

While there are other methods of introducing custom icons into a report, using DAX measures to introduce Unicode character icons is a fairly simple and lightweight method with a wide variety of options.