Power BI Legend Slicer from a JSON File with M and DAX

Data is king, or queen depending on your household dynamics. How you communicate that data and its impact to your clients can help or hurt your business. Both your short and long term relationships can hang in the balance, which is why the quality and delivery of your Power BI reports are everything.

Case Study: Power BI Report Development

In this blog post mini-series, we will be taking you through the process of creating a Power BI report. The demo report draws from one we created for a client as part of a larger dashboard project. It implements a line chart to visualize the weather on Mars.

The actual report we developed had nothing to do with Mars, space, or weather, but you should find it useful to understand how real-life issues can be resolved and optimizations can be employed. The first post in the series focuses on data-prep and implementation of a legend slicer.

Note: Each post in our series will display “Publish to Web” Power BI reports to illustrate functionality and show how the report looks at various stages of the design process. “Publish to Web” reports are much slower than reports you would see in a real Power BI environment. We recommend downloading the PBIX files and viewing them in Power BI Desktop or the Power BI Windows 10 app.

Background
We were doing a demo for a client recently of a Power BI report prototype that used a line chart. The chart displayed 14 line series, each in a different color, and the legend displayed the color and the name of the corresponding category. Pretty standard stuff.

The client instinctively clicked on the legend and his expected behavior for the visual was to dynamically turn the line series on and off by clicking on the legend. Unfortunately, that is not how the legend on a line chart works in Power BI. The only thing that happened when the legend was clicked was that the particular line series became highlighted. All of the other series were still present and the scale on the axis did not change.

We find the disparity between our clients’ initial interaction with the report and their expectations fascinating. They normally fall into one of two categories. 1) They can be kind of amusing, like my grandmother placing a CD on a record player and thinking it will play. 2) They inspire you to create intuitive functionalites that are not available out of the box.

In this case, our client’s interaction inspired us to develop an interactive legend slicer for their report. What follows is an explanation of the tools and concepts we used to create the legend slicer and for this demo we will use a simplified but interesting data set: weather on Mars.

The Data
NASA’s Curiosity Mars Rover landed on the Red Planet in August 2012 and began collecting weather data. The data points used in this example are daily high and low temperatures, atmospheric pressure, and the Mars-Sun angle which indicates when equinoxes occur. We grabbed this data in JSON format from a public github repo, which pulled  the information from NASA’s collection of open source data sets.

The goal is to have each of these daily values displayed on the same  line chart. The x-axis will indicate the terrestrial (earth) date. Martian days are a little different from ours, just over 24 and a half hours, but for simplicity we’ll keep our x-axis grounded here on earth.

This case study applies to any time for which you would want to view data points possibly related to one another. Having the option to view an individual data series by itself or dynamically selected combinations of data series at the same time can yield greater insights.

Data Prep & Loading the Data into Power BI
We knew we wanted the option of displaying the data in a logarithmic graph and Power BI has trouble doing that with negative values. Since Mars is cold, the temperature is rarely above freezing and the temperatures in the data set are conveyed in Celsius. This meant we could not use the data “as is” directly in Power BI. This is a “real-world” scenario present in the demo. Rarely does your data come directly in a usable format or optimized for your analysis platform.

We went ahead and converted the temperatures to Kelvin using M code. We also added a column that calculated the difference between the high and low temperatures because we wanted to visualize temperature difference as its own data series.

Performance within the Power BI reports is better when these row specific calculations are performed using M  in the “Get Data” phase as opposed to creating measures or columns in DAX after the data is loaded. Here is our M code for processing the downloaded JSON file:

let
Source = Json.Document(File.Contents("C:\swap\marsWeather.json")),
#"JSON List to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded JSON Record" = Table.ExpandRecordColumn(#"JSON List to Table", "Column1", {"id", "terrestrial_date", "sol", "ls", "season", "min_temp", "max_temp", "pressure", "wind_speed", "atmo_opacity"}, {"id", "terrestrial_date", "sol", "ls", "season", "min_temp", "max_temp", "pressure", "wind_speed", "atmo_opacity"}),
#"Assign Data Types" = Table.TransformColumnTypes(#"Expanded JSON Record",{{"terrestrial_date", type date}, {"sol", Int64.Type}, {"ls", Int64.Type}, {"pressure", Int64.Type}, {"max_temp", Int64.Type}, {"min_temp", Int64.Type}, {"id", Int64.Type}, {"wind_speed", Int64.Type}}),
#"Convert Min Temp C to K" = Table.TransformColumns(#"Assign Data Types", {{"min_temp", each _ + 273.15, type number}}),
#"Convert Max Temp C to K" = Table.TransformColumns(#"Convert Min Temp C to K", {{"max_temp", each _ + 273.15, type number}}),
#"Remove Unused Columns" = Table.RemoveColumns(#"Convert Max Temp C to K",{"wind_speed", "atmo_opacity"}),
#"Add Temp Delta Column" = Table.AddColumn(#"Remove Unused Columns", "delta_temp", each [max_temp] - [min_temp]),
#"Assign Data Type Delta Column" = Table.TransformColumnTypes(#"Add Temp Delta Column",{{"delta_temp", Int64.Type}})
in
#"Assign Data Type Delta Column"

Now we have the raw data loaded into Power BI.

Default Line Chart
The line chart in Power BI works well out of the box. Start by selecting an axis and the values you want displayed across that axis. Suppose we have a simple Line Chart with terrestrial_date as the axis and min_temp as a value.

If the value of the min_temp column is 200.15K on 3/16/2017 it will always be 200.15K on 3/16/17. As long as min_temp is our value, 200.15K will always be plotted for 3/16/2017.

In order to get a line series to appear or disappear based on a slicer selection, we need to have a value that is populated or blank based on a slicer selection. To make this happen we need two entities that we currently do not have:

1) A value in a different table that acts as a toggle to return a column’s value.

2) A measure in the data table (in this case marsweather) that returns a column’s value if the slicer value is selected or returns nothing if it is not.

GraphLines Table
Continuing with our min_temp example, I will create a GraphLines table by selecting “New Table” from the Modeling tab and create a two column table using the DAX expression below.

GraphLines =
DATATABLE (
"DataCol", STRING,
"GraphLabel", String,
{
{ "min_temp", "Low Temp (K)"},
{ "someothercolumn", "Some Other Column"}
}
)

Notice that we created a row for “someothercolumn” as well. That is because we need to select a value other than min_temp/Low Temp (K) to turn the series off.

Next, we will create the measure that returns the value of min_temp from our marsweather data table if the corresponding GraphLabel for the min_temp data column is selected from the GraphLines table. We will call this measure “LowTemp” and create it with the following DAX statement:

LowTemp = IF(CONTAINS(GraphLines,GraphLines[DataCol],"min_temp"),sum(marsweather[min_temp]),BLANK())

On our line chart we can now replace the min_temp column with our new LowTemp measure and the chart will look pretty much the same.

Once we add a slicer assigned to the GraphLines.GraphLabel column to the report canvas, we will be able to turn the line series on and off.

More Measures, Cutting & Pasting, Updated GraphLines Table, I need coffee….
Let’s go ahead and create the measures for our other data points in the marsweather table just like we did for min_temp:

We have now implemented a dynamic slicer. Ctrl+Click allows you to select multiple series and selecting none of the series shows all series.

Dynamic slicers are all well and good, but lets take this a little further to implement a legend slicer. To do this we will use one our favorite Power BI custom visuals: the Chiclet Slicer.

Chiclet Slicer
From our experience, the Chiclet Slicer does two things better than the built-in Power BI slicer. It has a “Forced Selection” property enforces one of the slicer options always being selected. It also allows you greater control over the slicer’s appearance.

For the purposes of a legend slicer, we want a slicer that lists the series available for the Line Chart (we have already achieved that functionality) and have the slicer display the color of that series (we don’t have that part yet).

It would be stupendous if the Chiclet Slicer had a “color” property and all we had to do was add a column to our GraphLines table that contained the color code for the series. Unfortunately the Chiclet Slicer does not currently have such a property but it does have an “Image” property.

The “Image” property is designed to display an icon or logo in a slicer to make a report more intuitive and appealing.
The image must come from a web URL. Local images cannot be assigned at design time and uploaded with the report.

The Chiclet Slicer can display an image, but we need it to display a color. At first glance, it doesn’t appear that the Chiclet Slicer will be able to accomplish our task of displaying our line series color along with the title of the line series.

Theoretically, we could create solid color images of each of our line colors, host them on a web server, and point a column in our GraphLines table to that URL. That would be a lot of work initially and to maintain whenever a line is added or modified. As I mentioned previously, the report we developed had 14 series displayed.

Creating an image from a URL
Luckily, we were able to find a service that dynamically creates solid color images based on parameters passed in a URL string. The service is dummyimage.com and is freely distributed under a MIT license. To create a color image you point the URL to https://dummyimage.com/{width}X{height}/{hexcolor}.png.

For example, a 4 pixel by 4 pixel PNG of hex color #025d56 would have the following URL:
https://dummyimage.com/4X4/025d56.png

Design Choices and a soapbox
Now lets make some color decisions for our report that suit the Red Planet better than the default colors and create the dummyimage URLs for them.

You notice that the colors we chose for Atmospheric Pressure and High/Low Difference appear similar. This is a conscious design choice.

Since we want our final product to have a particular look and feel, we are choosing warm colors. With that limitation, and others that will be introduced in the next blog article, there are only a handful of colors we can select from before they start looking similar. The actual report we developed had 14 line series and with that many, your are bound to get at least two that are hard to distinguish at first glance.

Atmospheric Pressure and High/Low Difference are the values that are furthest apart in magnitude. If two line series are going to share a similar color, it makes sense that they should be furthest apart. Since we are implementing a legend slicer, users can turn series on/off if they are confused, and tool-tips are present in the Line Chart to assist in identifying line series.

Most users who regularly consume dashboards and reports are very familiar with their data and how it can be visualized. While lines might look similar at first glance to the earth-bound eye, a Martian meteorologist would likely not confuse the two.

Throwing a lime green line on the chart for High/Low Difference would definitely set it apart, but it also would make the report more jarring. Considering the aesthetics of your presentation and the color schemes chosen to communicate information is important. Designing dashboard reports is part art and part science. From a design perspective, the most important thing for dashboard report designers to know is the data….and that could be a series of blogs in and of itself.

Updating GraphLines Table
Since we created our GraphLines table via DAX, we can add an ImgUrl column and its values by editing the DAX.

GraphLines =
DATATABLE (
"DataCol", STRING,
"GraphLabel", String,
"ImgUrl", STRING,
{
{ "min_temp", "Low Temp (K)","https://dummyimage.com/8X2/FF8899.png"},
{ "max_temp", "High Temp (K)","https://dummyimage.com/8X2/FDAE61.png"},
{ "delta_temp", "High/Low Difference (K)","https://dummyimage.com/8X2/FF4455.png"},
{ "ls", "Mars/Sun Angle","https://dummyimage.com/8X2/F27925.png"},
{ "pressure", "Atmospheric Pressure (Pa)","https://dummyimage.com/8X2/FF0000.png"}
}
)

Configuring the Chiclet Slicer
Adding the ChicletSlicer to a blank canvas and assigning our values with the default styling produces the following result
That is not very pretty. By making several configuration changes, we come up with something more usable.

Format > General > Columns: 1
Format > Chiclets >
    Text Size: 9
     Height: 130
Format > Images >
    Image Split: 30
    Stretch image:  On

The Legend Slicer
Placing the formatted Chiclet Slicer on the canvas with our line chart gives us a working legend slicer. I recommend viewing this full screen for best effect.

That just about does it for this post. In the next post, We will introduce more slicers, dynamically changing the chart scale from linear to logarithmic, and more design choices. We might even throw-in references to Elton John and David Bowie songs.

In the meantime, if you or anyone on your team has any questions about data operations, dashboards and reports, contact one of our BI specialists at info@Mi4.com or visit Mi4.com.