Power BI Switching between Logarithmic and Linear Scales

Welcome to the second post in our miniseries: “Are You Developing Power BI Reports the Right Way?” In the two-part series we are designing a sample Power BI report visualizing the weather on Mars and using some real world techniques.

The highlights of our first post were:

- Getting data from Mars
- Using a JSON file as a data source
- Performing operations in the “Get Data” phase using M
- Implementing a Dynamic Slicer
- Using Chiclet Slicer and Dummyimage to create a Legend Slicer
- Making design decisions

Note: Due to the popularity of this article, we have developed a custom Power BI line chart visual that is published to the Power BI AppSource Marketplace and has built-in support for allowing the end users to switch the axis scales, read about it in @Talal’s post. and viewing them in Power BI Desktop.

This post will focus on:

- Switching an Axis Between Logarithmic and Linear Scales via a Slicer
- Adding a Date Slicer
- Using a Dark Theme

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.

This is what our Mars Power BI Report looked like after our first post:

Logarithmic Scales in Power BI Visuals

As of this blog post’s publication (the St Louis Blues just won their 1st Stanley Cup and Canada has their 1st NBA Championship) Power BI supports logarithmic axes…sort of. Linear axes are the default. To change a visualization’s axis to logarithmic scale:

Select the visualization and in the Visualizations tab go to the Format Options
Expand the X or Y axis property
Select “Log” from the scale type drop-down.

If you followed these steps and did not see “Log” as an option, it is not because we like pulling our readers’ legs. It is because Power BI only supports logarithmic axes for values greater than zero. If a zero or negative value is present on an axis based visualization, Power BI will not display “Log” as a Scale Type option. If you selected the “Log” scale and the data in the visualization later changed to contain a zero or negative value, the axis property will have an icon alerting you to an automatic linear scale axis conversion.

The axis options for: (a) a supported log axis, (b) a previously log supported but automatic change to linear axis, and (c) a non-log supported axis are shown below respectively.

We mentioned in the previous post that we converted the temperatures from Celsius to Kelvin to avoid zero and negative values for this reason. But even with all of the temperature values above zero, the SunAngle value (abbreviated as “ls” in our data table) would still hit zero once a Martian year[*].

In order to support a Log axis, we will need to alter the value of SunAngle to “BLANK” when it hits 0. Previously, the DAX for the SunAngle measure was:

SunAngle = IF(CONTAINS(GraphLines,GraphLines[DataCol],"ls"),sum(marsweather[ls]),BLANK())

To achieve the result we want, we will add another IF condition to the DAX for when the sum of the “ls” value is zero.

SunAngle = IF(CONTAINS(GraphLines,GraphLines[DataCol],"ls"),IF(sum(marsweather[ls])>0,sum(marsweather[ls]),BLANK()),BLANK())

Now we can select a Log scale for our Line Chart’s Y Axis and see what our data looks like with a logarithmic scale.

Logarithmic / Linear Slicer

To add the ability to switch between a Log and Linear Y axis scale, we will need to configure the chart to have a logarithmic Y axis scale and then “break” it by displaying a zero. This will cause Power BI to automatically convert the axis to a Linear scale.

To implement this we took an approach similar to the dynamic slicer in the first post:

Create a table with the values that will be used in a slicer
Add a slicer and associate the values from the new table
Create a measure in our main data table (marsweather) that changes based on a slicer selection
Add this measure to the Line Chart

Since this will be a two item slicer acting as a toggle, we will create the table by using the “Enter Data” activity off of the “Home” tab.

Rename “Column1” to “ScaleType”
Enter values “Linear” and “Log”
Name the table “Scale”

We then place another Chiclet Slicer on the canvas and associate it with the ScaleType item. This time we will turn “Multiple selection” off and “Forced selection” on in the Chiclet Slicer’s options to achieve toggle functionality.

LinearLog DAX Measure

To make Power BI switch from a Log scale to a Linear scale, you need to force the visualization display a zero or negative value and revert to a linear axis. We will accomplish this by creating a measure called LinearLog in our marsdata table. The DAX for the measure is:

LinearLog = IF(SELECTEDVALUE(Scale[ScaleType]) = "Linear", CALCULATE(sum(marsweather[pressure]),LASTDATE(marsweather[terrestrial_date]))*0,BLANK())

The measure is looking to see if “Linear” is the selected value of Scale[ScaleType]. If it is, we will force a zero value for the entire selected time range by multiplying the marsweather[pressure] value by zero [**]. The zero series is not visible when anything other than “Linear” is selected because it is BLANK() .

The Power BI Report below now has “Linear” and “Log” buttons to toggle the Y axis scale on the line chart. You will notice a light blue line along the X axis when “Linear” is selected. This is the zero-series we are adding to the chart to force a linear scale. Later in the post, we will make this line virtually invisible by setting it to the same color as the X axis.

Date Slicer

We will add a simple date slicer to the report for the end users to view the weather data by terrestrial year. We could use a regular slicer to slice by year, but in order to keep a more cohesive design, we will go ahead and use another Chiclet Slicer.

We placed a Chiclet Slicer on the canvas and assigned the Category to marsweather[terrestrial_date] and then to the “Year” level in the default date hierarchy. We further formatted the slicer to be one column to match our other slicers.

Going Dark

In our previous post, we touched on design decisions – specifically choosing warm colors to represent the Red Planet. To finish off this two-part series we will make more design decisions to add a little polish to the report. In keeping with the outer space motif, we will implement a dark theme.

The first place we’ll start is the background. For this exercise, we opened PowerPoint and browsed some theme backgrounds until we found a dark background we thought might work. It is not anything too fancy; a subtle gradient with some texture.

From PowerPoint, we saved the slide as a .png and then set it it as the background of our report in Power BI.

Next, we formatted the Chiclet Slicers with colors for a dark theme. We chose the following color scheme for the Linear/Log and Year slicers:

We liked how the orange selected color looked but we could not use it for the Legend Slicer because it might interfere with the legend line colors we added in the first blog post. Instead we went with this configuration which gives the selected item a more “pressed down” look in the Legend Slicer:

Next, we lightened up our X and Y axes on our Line Chart from the default dark gray to a silver (Hex #CCCCCC) so that they would be more legible but not too stark. Finally, we set the data color for our LinearLog series to the same color as our axes. This way, when “Linear” is selected from the slicer, the “zero” line we added to switch the Y axis scale will blend in to the X axis.

Our finished product now looks like this:

We hope you’ve enjoyed this two-part case study where we have demonstrated a handful of techniques that we have implemented in real world scenarios.

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.

Footnotes

[*] A line chart is not the best visualization for the SunAngle, which is really a periodic indicator. We put it on the line chart to have another line series to “show off” the legend slicer. In a real world scenario, a cyclical visualization would be better. I can see it now “Ziggy Stardust and the Spider-charts from Mars.”

[**] Multiplying a value by zero is a “hacky” approach, but we do this to force a value across the time series. If we were using a DATE table, the DAX formula would be more elegant. However for the purposes of this post, we are trying to limit the number of tables created.

Power BI Switching between Logarithmic and Linear Scales

Related

One Reply to “Power BI Switching between Logarithmic and Linear Scales”