Enabling the modern data analyst by becoming shopkeepers rather than gatekeepers of information
I appreciated the opportunity to sit down and talk with one of my heros in the industry. Donald Farmer has been a recognized leader in the Business Intelligence and Analytics community for the past two decades and his thought leadership is even more relevant today. He played a key role in shaping the Microsoft BI toolset as a Principal Program Manager from 2001 when the products we use today were in their infancy. He’s also been outside of Microsoft circles which gives him a broad and unique industry perspective. As the Vice President of Innovation and Design at Qlik Technologies, he brought Qlick Sense to market. He remains a friend and advocate for the community. Donald holds multiple patents and has published multiple books. Today he is doing his own thing as the Principal of TreeHive Strategy.
The chat was 9:41 long and is worth watching every second. I have added captions to highlight a few key points which are also listed below. Please enjoy.
Route Knowlege is a view of information at a micro level, like turn-by-turn directions. This might be important when you only need to answer a single question without a broad understanding of the entire landscape. By contrast, Survey Knowlege is a view of information in the context of the bigger picture. Someone with Survey Knowlege knows “how the whole thing hangs together“.
Serving the needs of the modern Business Data Analyst requires a change in attitude. We should become provisioners of information rather than protectors of information. In other words, become shopkeepers rather than gatekeepers.
Actionable data is not always detail data and consuming a summation can not only be easier but more useful than getting lost in the details and the accuracy or precision of a number. Drilling to detail in context is a natural part of the exploration process. He talked about a pattern in the process to provide actionable business information, which includes these stages:
I appreciate Donald taking the time to both speak at PASS Summit and to chat with me for this blog series. Please watch my blog for additional interviews and insights from PASS Summit 2019
Power BI table and matrix visuals can render SVG object elements in a column or measure to display in-line web content. You can build dynamic content like KPI indicators, databars and sparklines by altering SVG element content using DAX expressions. In this post, I will show you how to create databars that show negative and positive values with different color fills based on category values. Of course, Power BI has built-in databars in the conditional formatting options for numeric columns but you cannot alter the color and other attributes dynamically – at least not yet. For the time being, this technique provides more flexibility than the built-in databar feature.
Read This First
In this post from earlier this week, I introduced a simple technique to produce symbols and graphics using columns or a measure to render SVG images in a table visual. I recommend that you read that post to understand the simple implementation of this technique.
Instead of a graphic symbol, the databar really just consists of a rectangle that has a variable start and end position. I want to change the fill color layer based on whether the value is negative or positive, and based on a category value in my data. You could apply any imaginable business logic to change the color or other attributes. I also want to layer formatted text over the top of the databar rectangle. Check out the example below showing a series of records. My simplified demo dataset causes the same value to repeat across the four Flintstones characters but this Databar measure could be applied to any set of data. Notice that negative values are red and the bar “starts” at the center and is proportionally longer as it extends to the left of center for larger negative values. The same is true for positive values that start in the center of the cell and move to the right.
Before we examine the code to generate the dynamic databar, let’s look at the rendered SVG element for a couple of these cells. If I hover over the cell for ID 1 and “Barney”, the SVG definition is displayed in the tooltip.
One fun thing about writing this blog post and showing you fragments of HTML script is that it is a little challenging to copy and paste the script into this post without it being interpreted and rendered as a graphic. This is one reason I’ve elected to use screen capture images.
Examine the above script and you will see the “<rect” element opening tag followed by attributes that specify the x axis, y axis and height values. These attributes draw the rectangle starting at 79 pixels from the left-most edge of the viewBox (left side of the cell), 80 pixels tall and 181 pixels wide. Keep in mind that the graphic does get rescaled with the table so these are only relative measurements. The fill property of the style attribute fills the rectangle with the color Red.
There is also a “<text” element with similar attributes that place centered (anchored to the middle of the viewBox) text at the mid-point of the viewBox width of 300 pixels. For simplicity, there are a few attributes I’m not mentioning but it’s fairly simple to recognize them and figure out what’s going on.
Hovering over the cell for ID 9 and “Barney” with the value 1495, the x and y axis values cause a DodgerBlue filled rectangle to start at 260 pixels and to be 236 pixels wide.
Show Me the DAX
The DAX code for the DataBar measure is below. The actual expression code is fairly simple but it took a little trail-and-error to work-out the measurements and scaling logic. Rather than building the expression in-line, I’ve invested work up-front to put all the values into variables. First, we get the minimum value for all the selected ID rows (using the MINX iterator function with the ALLSELECTED filtering function) and store that in the MinValue variable. The MaxValue variable is similar with the added condition to handle negative or positive values. This may all seem a bit verbose but its really just math.
There is a disconnected table in my sample data model named Flintstone Characters with character names that I place onto the Columns axis of the matrix visual. In a real-world solution, this could be a field like product categories, geographic regions or anything else. The BarFill variable is set to a color value (which could be either a web color name or Hexadecimal value) based on the selected character name. When grouping a visual on a value, the SELECTEDVALUE function will return a single value. After resolving all the variable values, this measure emits the SVG markup similar to the examples shown previously.
DataBar = VAR MinValue = MINX(ALLSELECTED(‘Values'[ID]), calculate(SUM(‘Values'[Value]))) VAR MaxValue = MAXX(ALLSELECTED(‘Values'[ID]), calculate(SUM(‘Values'[Value]))) + IF(MinValue<0, ABS(MinValue), 0) VAR BarValue = SUM(‘Values'[Value]) VAR FormattedValue = FORMAT( BarValue, “#,##0” ) –“#,##0.00” –“#,##0.0 %” VAR svgWidth = 600 VAR svgHeight = 200 VAR svgFontStyle = “normal 70px sans-serif” VAR BarHeight = 80 Var BarRange = ABS(MinValue) + MaxValue VAR BarValueFactor = DIVIDE(svgWidth, BarRange) VAR BarWidth = INT( ABS(BarValue) * BarValueFactor ) VAR BarZero = INT( DIVIDE(ABS(MinValue), MaxValue) * svgWidth ) VAR BarStart = IF(BarValue < 0, BarZero – BarWidth, BarZero) VAR BarOpacity = “0.5” VAR BarFill = IF( BarValue < 0, “Red”, SWITCH( SELECTEDVALUE(‘Flintstone Characters'[Character]), “Fred”, “DarkOrange”, “Wilma”, “Purple”, “Barney”, “DodgerBlue”, “Betty”, “DarkGreen” ) ) VAR svg_start = “data:image/svg+xml;utf8,” VAR svg_end = “” VAR svg_rect = “” VAR svg_text = “” & FormattedValue & “” // x=(half of width width when text is centered) RETURN svg_start & svg_rect & svg_text & svg_end
Like in the Symbols example in the earlier post, you must select the measure and set the Data Category in the Modeling ribbon to Image URL. After that, the measure can be dropped into a matrix visual grouped on the ID and Character fields, to produce the databar visuals I showed in the first example above.
It Takes a Village
A few experts in the community have created SVG sample projects with various techniques. David Eversveld has blogged about generating SVG images in this series. When I started working out the code for the DataBar technique, I reached out to some friends in the MVP community and Darren Gosbell was kind enough to help-out. In the process Darren discovered a bug (or an undesired behavior) in Power BI that ignores the aspect ratio directive in the SVG element and draws a square image even when you don’t want it to. He posted a suggestion the Power BI Ideas forum and I encourage you to go vote to help get this fix prioritized by the Power BI product team. Before this is corrected, the technique is useful but will take-up more vertical space than you might prefer.
Creating in-line Sparklines
On a related topic, Brian Grant has developed a method to produce SVG trend line charts, commonly known as “sparklines”, using a creative string replacement technique. You can find a tutorial for his solution here.
With so many different methods to visualize data – just within Power BI – this can be a daunting choice. Let’s see… there are a plethora of standard visuals that can be used in creative ways. A software developer can create a custom visual if they have the time and chops for that kind of thing. You can use a custom visual generator tool like Charticulator or the Chart designer from PowerBI.Tips. And, you can render unique visuals for statistical analysis using R or Python. The technique I’m going to demonstrate in this series doesn’t require a lot of work or special skills so it is relatively simple and easy to use.
In this first post in a series called “Next Level Power BI”, I demonstrate how to create a simple graphical symbol as a Scalable Vector Graphic – just to get started. In a later post, we will expand the technique to create dynamic databars. The same technique can be used to create icons, sparklines and practically any imaginable graphic can be produced with calculated column or measure.
I’ll start with a simple example. Let’s say that you need to display a set of symbols to correspond to a data condition of some kind. For simplicity, let’s just start with the graphics. Here are examples of three symbols displayed in a table visual on a Power BI report page:
Finding the SVG markup for these symbols can be as simple as a web search. You can also create your own using vector graphic editing tools like Adobe Illustrator. The markup for these three symbols is shown here in Power Query. In different Power BI solutions, I have stored and exported the SVG markup in Excel. It’s just text so you can store it anyway you prefer. All three symbols are defined as a series of spatial points in the path element but you can draw shapes, text and a variety of things. There are several good SVG references on the web but I found this collection from Jakob Jenkov to be particularly useful: http://tutorials.jenkov.com/svg/index.html
In this solution, I just used the Enter Data feature and then copied and pasted the SVG markup directly into the query – one row per image.
We’re not quite done because Power BI requires the following header element to be appended to the beginning of each graphic:
You can approach this a few different ways but I added a custom column named Definition and then removed the original column using this expression:
After closing and applying the query, to render the SVG image which exists in the Definition column within a report; it must be categorized as an Image URL. In the report designer, select the column from the Field list and then choose the Data Category on the Modeling ribbon.
The column is now usable as an image in a table or matrix visual, which should appear like the first screen image above.
To create a measure to display only a single image, you can create a measure like this one that returns only one value from the column:
The measure must also be categorized as an Image URL and then it can be used as an individual symbol or image in a report within a table visual.
That covers the bare bones basics of SVG image columns and measures. There is so much that you can do, expanding on this technique by injecting dynamic logic into the measure code. I’ll cover that in a subsequent post.