John Dalesandro | Technology to get things done

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.

Power BI Desktop - Formatted Table Visualization — Formatted Table Visualization Using Conditional Formatting, DAX Measures, and Unicode Characters

Power BI Desktop - Formatted Card Visualization — Formatted Card Visualization Using DAX Measures and Unicode Characters

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.

Power BI Desktop - Data Sources — Power BI Desktop – Data Sources

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.

Power BI Desktop - Load Data — Power BI Desktop – Load 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.

Power Query - Use First Row as Headers — Power Query – 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 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)

Power BI Desktop - Add New Column to Map Text Values to Numeric — Power BI Desktop – Add New Column to Map Text Values to Numeric

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.

Power BI Desktop – Unformatted Table Visualization

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.

Power BI Desktop – Partially Formatted Table Visualization

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.

Power BI Desktop – Final Formatted Report (Green Status)

Power BI Desktop – Final Formatted Report (Red Status)

Revisiting NES Classics Using Nintendo Switch Online Rewind Feature

Ghosts ‘n Goblins is the most difficult NES game I own. Battletoads is certainly challenging in spots like the Turbo Tunnel, but it doesn’t seem as punishing as Ghosts ‘n Goblins. As a kid, I spent hours (and hours) trying to make my way through this game. It requires repeated grinding through every inch of every stage to learn where to move in order to defeat enemies coming from every direction. Simply finishing the initial graveyard stage feels like a great accomplishment.

Ghosts 'n Goblins - Box Art — Ghosts ‘n Goblins – Box Art

The level design has enemies in the worst possible spots. The game mechanics make the Knight very hard to maneuver while the enemies are able to move freely and at high speed. The game also suffers from slowdown if too much is happening on screen at the same time (which seems to happen most of the time). According to the box art, this represented “state-of-the-art” and “high resolution graphics”. One hit causes the Knight to lose his armor and proceed only in his skin and red underwear. A second hit makes you a pile of bones.

Ghosts 'n Goblins - Pile of Bones — Ghosts ‘n Goblins – Pile of Bones

Any enjoyment from this intense challenge eventually turned into complete frustration. After spending hard-earned dollars to buy the game, it was disappointing to admit defeat and put the game cartridge away to collect dust.

Many years later, I signed up for the Nintendo Switch Online primarily to play Super Mario Bros. and The Legend of Zelda without having to dig out my old NES and some crusty controllers. As I looked through the catalogue of classic NES games, Ghosts ‘n Goblins caught my attention. Maybe it wasn’t as difficult as I remembered. Maybe my skills improved during those intervening years.

No, it is still impossible. At this point, I couldn’t even make it through the first stage. I was about to put the game back on the digital shelf to collect virtual dust.

Instead, I found that all of the classic games in the Nintendo Switch Online library have a “rewind” feature. If the Knight is hit and down to his red underwear, I can rewind the game to any point up to a minute or so earlier. Is it cheating? Perhaps – but it certainly makes the game more enjoyable. I still have to make all of the jumps and get through the enemy hordes, but the ability to rewind removes most of the frustration. I don’t have the hours (and hours) to dedicate to this game as I had as a kid so this feature allows me to continue making progress without starting over after each mistimed jump or the flying attacks from the Red Devil.

With the rewind feature, I was able to make it to the end of the game after so many years…only to discover that you have to play through the whole thing a second time to get to the real ending!

Ghosts 'n Goblins - Nintendo Switch Rewind — Ghosts ‘n Goblins – Nintendo Switch Rewind

Creating Cascading Drop-down Lists in Excel

If you’re using Microsoft Excel to capture and track data, one of the challenges is maintaining good data quality when more than one person is updating the workbook. The data validation features in Excel help by only allowing the user to select data based on pre-defined options in a list. This feature works well on individual cells. However, if you have a column that depends on the value in a different column, you will need to get a little more creative. This post describes the steps for creating cascading drop-down lists in Excel using a combination of data validation and named ranges.

Step 1 – Define the Reference Data

Let’s assume that we are collecting data that includes Organization and Department attributes. In this example, an Organization is the parent to one or more Departments (children). We’ll set up the reference data for the data validation drop-down lists as follows on its own worksheet.

Column A (“ORGANIZATIONS”) represents the list of valid Organizations to appear in the Organization drop-down list.
Column B (“ORGANIZATION TO DEPARTMENT MAPPINGS”) represents the names given to ranges that will be defined in a later step.
Columns D through E (“REF_ORGANIZATION_X_DEPARTMENTS”) represent the lists of valid Departments to appear in the Department drop-down list depending upon which Organization is selected.

Excel Cascading Drop-down List - Reference Data — Reference Data

Step 2 – Define the Named Ranges

Now that we have the reference data established, we can define the named ranges that refer to these lists. The named range entry defined as REF_ORGANIZATION_TO_DEPARTMENT_MAPPINGS is the key to creating the cascading drop-down list functionality. It is important that the values in Column B (above) match the names given to the REF_ORGANIZATION_X_DEPARTMENTS ranges. This value is used in a subsequent step using a combination of INDIRECT and VLOOKUP in the data validation formula.

Excel Cascading Drop-down List - Named Ranges — Named Ranges

Step 3 – Define the Organization Data Validation (Parent Column)

In this step, we establish the standard data validation on the parent data in Column A (“REF_ORGANIZATIONS”) on a new data collection worksheet.

Excel Cascading Drop-down List - Parent Level Validation — Parent Level Validation

Step 4 – Define the Department Data Validation (Child / Dependent Column)

Here we establish the data validation rule that performs the cascading drop-down list function. Once the user selects a valid Organization, this formula performs a VLOOKUP against the REF_ORGANIZATION_TO_DEPARTMENT_MAPPINGS named range and returns the name of the Department named range, e.g. REF_ORGANIZATION_X_DEPARTMENTS, associated with the selected Organization. The indirect function then converts that name, a text value, into the named range reference.

=INDIRECT(VLOOKUP(A2,REF_ORGANIZATION_TO_DEPARTMENT_MAPPINGS,2,FALSE))

Excel Cascading Drop-down List - Child Level Validation — Child Level Validation

When you click the OK button, Excel will display the following error message. Click the Yes button to continue.

Excel Cascading Drop-down List - Validation Error Message — Validation Error Message

Step 5 – Test the Cascading Drop-down Lists

Now that the data validation rules are set up, we can test the cascading drop-down list functionality. Selecting “Organization 1” causes the validation drop-down list in the Department column to reflect only those Departments associated with “Organization 1”. Selecting “Organization 2” causes the Department drop-down list to show those Departments associated with “Organization 2”.

Excel Cascading Drop-down List - Parent List Values — Parent List Values

Excel Cascading Drop-down List - Child List Values Based on Parent Selection — Child List Values Based on Parent Selection

Both Parent and Child Selected

Step 6 – Macro to Validate Parent / Dependent Relationship

Now that the worksheet is functioning as expected, you release it for users to update and someone will inevitably enter data in a way that breaks the parent / child relationship. As an example, “Organization 2” is selected in the Organization column and “Department 6” is selected in the Department column. The user then returns to the Organization column and changes the entry to “Organization 3”. The Department column retains the value of “Department 6” which is not valid for the “Organization 3” selection in the Organization column. To help avoid these errors, you can save the file as a macro enabled workbook and add the following code to the data entry worksheet. If this macro had been enabled, the “Department 6” value is removed as soon as the user selected “Organization 3”. This code assumes that the parent column is the first column (Column A) in the worksheet and the child column is immediately to the right (Column B). Please revise the code to meet your specific requirements.

Selecting Parent Field Clears Child Field Through Macro

Example

Source Code

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo exitHandler
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    If Target.Column = 1 Then
        If Target.Validation.Type = xlValidateList Then
            If Not Target.Offset(0, 1).Validation.Value Then
                Target.Offset(0, 1).ClearContents
            End If
        End If
    End If
exitHandler:
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub