Power BI: Unicode Characters in Visualizations

A common requirement is to convert text-based values into colorful icons in Table or Card visualizations. Tools like Microsoft Power BI and TIBCO Spotfire make it easy to map specific data values or ranges to default icons through conditional formatting. However, displaying an icon in a Card visualization in Power BI is more complex, requiring a combination of DAX (Data Analysis Expressions) measures and Unicode characters.

The image below shows two methods. The STATUS column uses the default icons from conditional formatting in a Table visualization. The MEASURE STATUS ICON column uses a DAX measure to output a Unicode character, which is then colored with another DAX measure and conditional formatting. I find the DAX method more versatile, as it can also be applied to other visualizations, like the Card visualization shown in the second image.

Both methods will be demonstrated, with the DAX method as the main focus.

Instructions

Step 1: Create Underlying Data

To demonstrate this, I have a list of PROJECT NAME and STATUS pair values in an Excel worksheet. The valid STATUS values are:

• Not Started
• On Track
• At Risk
• Off Track
• Complete

Step 2: Import Data

Begin by creating a new report in Power BI and importing the Excel file with the PROJECT NAME and STATUS list.

Click Import data from Excel and select the file.

Step 3: Transform Data

During the import process, you may need to transform the data if the column headings aren’t recognized. In the screenshot below, the columns are labeled Column1 and Column2 instead of PROJECT NAME and STATUS.

1. Click Transform Data.

2. The Power Query editor opens.

3. From the Home ribbon, in the Transform section, click Use First Row as Headers.

4. Select Use First Row as Headers.

Step 4: Convert Text Values to Numbers

In this step, the text-based STATUS values are mapped to numeric values. This makes conditional formatting and DAX measures easier to configure later. It also reduces maintenance, as it minimizes the number of places where text values need to be updated if someone requests a name change.

1. Switch to the Table view.
2. From the Table tools ribbon, click New column.

Using a SWITCH function, the text-based STATUS values are mapped to numeric values. I used increments of 100, but you can choose any unique 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 in the STATUS NUMERIC column, these can be used in two new DAX measures to map to Unicode characters and hex color codes.

1. From the Home ribbon, click New measure.
2. Add the following code to create the ICON measure.
3. Repeat this process to create the ICON COLOR measure.

The numbers in the UNICHAR function correspond to their equivalent HTML codes.

• Figure Dash ‒ 8210
• Black Large Circle ⬤ 11044
• Black Up-Pointing Triangle ▲ 9650
• Black Diamond ◆ 9670
• 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 simple terms, the hex color codes represent black, green, yellow, and red (though these are not the same as the HTML color names). I’ve also excluded the 500 value, as its default Unicode color is already as expected.

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

1. Switch to Report view.
2. Add a Table visualization.
3. Add the PROJECT NAME, STATUS, and MEASURE STATUS ICON fields as Columns in the table.

In the next steps, we’ll add default icons to the STATUS column using conditional formatting and apply color to the MEASURE STATUS ICON using a DAX measure. As shown in the screenshot, the DAX measure mapping numeric status to Unicode characters is working.

Step 7: Conditional Formatting for Default Icons in Table Visualization

1. Click the STATUS field in the Columns list for the Table visualization.
2. Select Conditional Formatting, then Icons.
3. Follow the screenshot to configure the formatting using the numeric values defined earlier. This will display icons to the left of the text values in the STATUS column of the Table visualization.

After applying conditional formatting, the STATUS column in the Table visualization will show icons.

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.

1. Click the MEASURE STATUS ICON field in the Columns list for the Table visualization.
2. Select Conditional Formatting, then Font color.
3. Follow the screenshot to configure the formatting using the DAX measure that maps numeric status to color.

The Table visualization is now fully formatted using both methods.

Step 9: Create Card Visualization

In Report view, add a Card visualization and include the MEASURE STATUS ICON field in the Fields.

In the next steps, we’ll apply conditional formatting to the STATUS column for default icons and use a DAX measure to add color to the MEASURE STATUS ICON. As shown in the screenshot, the DAX measure mapping numeric status values to Unicode characters is working.

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

1. Click the Card visualization and go to its Format visual options.
2. Under Callout value, click the fx button for Color.
3. Configure the options as shown below.

Result

Now that everything is set up, both the Table and Card visualizations are working as expected. The Unicode character icons are displayed and colored in both visualizations. When a specific entry in the Table visualization is selected, the Card visualization shows the corresponding Unicode character with the correct color.

While there are other ways to add custom icons to a report, using DAX measures to display Unicode character icons is a simple, lightweight method with many options.

Summary

In conclusion, using DAX measures to display Unicode character icons in Power BI is a simple yet versatile method that can be applied to various visualizations, such as Table and Card. This approach provides flexibility, allowing for easy customization and coloring of icons, while also reducing maintenance efforts when updating text values.