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 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())
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.
- 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())
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