Deneb & Vega-Lite Walkthrough Series | EP06: EXPRESSIONS & CONDITIONAL FORMATTING (pt1)š
Vega-Lite expressions are both flexible and powerful. They WILL change your dataviz life for the better, opening up a world of near-unlimited viz formatting possibilities. šļø š§š¼āāļø āØ
š PBIX file available at the end of the articleā¦ Enjoy!
Intro
I am excited to write this article and I am excited for you because you will hopefully unlock your abilities to unleash next-level wizardry in your Deneb data visualisations. It might get a little intense, a little wild and confusing at times š¤Ŗā¦ but if we break it down into little pieces, the penny will drop for you like it did for meš„². This episode will be the icing on the cake šwith the cherry š on top! šš½
Thereās a lot to get through, so make sure you are hydrated and well-nourished ā we need all the help we can get š
Expressions
What are expressions? š¤Think of them as data manipulations or transformations. They have the same effect as creating a DAX measure with conditional logic for conditional formatting.
If we start by referring to the trusted Vega documentation, you will soon realise you have entered a world of pain. Nested deep in the Expressions section, there is a small paragraph on something called Control Flow Functions š¤Ø
and elsewhere we see:
āControl whatty what-nowā¦ Ternary Opera-whatchimacallit??š¤Æ
To mere mortals, we are talking about IF statements! Nice, safe, easy(-ish), comfortable, familiar IF statements šš½
Weāll take a look at the DAX examples first.
Conditional Formatting
DAX Examples
Below are 2 examples of typical conditional formatting measures using the SWITCH() function.
That is indeedā¦ tremendous.
Vega-Lite Examples
Next, weāll do the exact same with Vega-Lite in Deneb. This is a quick glance, weāll dig into the detail shortly.
"layer": [
{
"mark": {
"type": "bar",
"width": {"band": 0.65},
"xOffset": 0,
/* -----------------------------------------------------------------------
conditionally format the mark's colour property, if the column data value
equals 1 then return the blue colour otherwise return the grey colour
------------------------------------------------------------------------*/
"color": {
"expr": "datum.isYTD_INT == 1 ? '#004385' : '#EDEDED'"
},
//----------------------------------------------------------------------\\
"stroke": "black",
"strokeWidth": 1
}
}
]
Can you see the similarities between the DAX and VL methods? We achieve the same result, with similar techniques, just with a different coding langauge.
This is the starting point. We eventually want to end up with this result šš½
Now letās break it down, step by step. I will show you two examples:
- One with Power Query āhelperā columns
- One using in-line VL transformations and expression, and no helper columns
1. Conditional Formatting with Power Query columns (dimension table)
Step 1: House Keeping ā preparing variables/parameters for tidier coding
As you will have experienced, in any conditional formatting situation, the lines of code can explode rather rapidly, which can be overwhelming to manage and maintain, and makes reading the code somewhat more difficult. Weāll organise our code in such a way as to reduce duplication and redundancy, and make it more pleasant to peruse.
To provide the foundations, we first want to copy and paste this code into the Config tab. The intention is to create a consistent theme for all chart elements in terms of fonts, axis & label fonts, and X & Y-axis chart design. In terms of fonts, I donāt think there is a way to set a āglobalā font, we have to declare the font for the various chart properties. Iāve used parameters (params), which are similar to declaring variables (var) in DAX.
Curveballā¦ the term variable is a misnomer, a variable is better understood as a constant or a parameter š«
You can think about a variable as a name for an expression. The term āvariableā itself is somewhat misleading ā a DAX variable cannot change its value, as the name would suggest. A DAX variable is indeed a constant, meaning that it is a name for the value computed by the definition of the variable.
ā SQLBI (Marco Russo & Alberto Ferrari)
Back to the lessonā¦paste the code sample below into the Config tab. Iāve added annotation for illustrative purposes.
{
"view": {"stroke": "transparent"},
/*------------------------------------------------------------------
Declare parameters/variables
------------------------------------------------------------------*/
"params": [
{
"name": "globalFont", //<-- I create a parameter called "globalFont"
"value": "segoe ui" //<-- define the value for the globalFont parameter (segoe ui font name)
},
{
"name": "globalFontAxis", //<-- define another parameter called "globalFontAxis"
"value": "segoe ui semibold" //<-- define the value (segeo ui semibold font name)
}
],
/*------------------------------------------------------------------
define or format individual axis objects
------------------------------------------------------------------*/
"axis": { //<<--- open global axis properties
"labelFont": {
"expr": "globalFontAxis"
}
// "labelFont": "segoe ui" //<-- standard method for formatting options
,
"labelFontSize": 16,
"titleFont": {"expr": "globalFont"},
"title": null,
"tickCount": 4,
"ticks": false,
"domain": false,
"gridDash": [2, 4],
"gridOpacity": 0.4
}, //<<--- close global axis properties
"axisX": { //<<--- open X-axis properties
"ticks": true,
"domain": true
}, //<<--- close X-axis properties
"axisY": { //<<--- open Y-axis properties
"labelPadding": 18,
"ticks": false,
"domain": false
}, //<<--- close Y-axis properties
"mark": {
"font": {"expr": "globalFont"}
}
}
And in the Specification tab, weāll copy and paste the base chart spec:
{
"data": {"name": "dataset"},
"params": [], //<-- blank params array (we'll return to ths later)
"transform": [], //<-- blank transforms array (more on this later also)
"layer": [
{
"mark": {
"type": "bar",
"width": {"band": 0.65},
"xOffset": 0,
"color": "#004385", //<-- colour value
"stroke": "black", //<-- stroke value
"strokeWidth": 1 //<-- stroke width
}
}
],
"encoding": {
"x": {
"field": "Date",
"type": "ordinal",
"timeUnit": "yearmonth",
"axis": {
"title": null,
"format": "%b-%y"
}
},
"y": {
"field": "AC",
"type": "quantitative",
"axis": {
"format": "Ā£0,,.#M",
"formatType": "pbiFormat"
}
}
}
}
Step 2: Utilise parameter values
Now we have a few options where we can place the parameter, either within the Spec or the Config, thereās no right or wrongā¦ just a matter of preference. Iāll show both, the effect is exactly the same.
Option 1: Spec
First, Iāll make an intentional mistake to emphasise how things can go wrong and to highlight where we need to take extra care in coding in parameters.
{
"data": {"name": "dataset"},
"params": [
{ // <-- add parameter between curly braces
"name": "_barColour", //<-- parameter name
"value": "#004385" //<-- parameter value
} // <-- close braces
],
"transform": [],
"layer": [
{
"mark": {
"type": "bar",
"width": {"band": 0.65},
"xOffset": 0,
// "color": "#004385" <-- original code
"color": "_barColour", //<-- reference your parameter name (p.s this isn't going to work)
"stroke": "black",
"strokeWidth": 1
}
}
],
"encoding": {
"x": {...},
"y": {...}
}
}
and the resultā¦
But why doesnāt this work? Letās consider what the VL code is saying:
{
"data": {"name": "dataset"},
"params": [
{ // <-- add parameter between curly braces
"name": "_barColour", //<-- parameter name
"value": "#004385" //<-- parameter value
} // <-- close braces
],
"transform": [],
"layer": [
{
"mark": {...
/* -----------------------------------------------------------------------
ā¢ here, the color object is looking for a colour value value "#004385"
ā¢ this is a proper colour HexCode (polynesian blue) and works fine
----------------------------------------------------------------------- */
"color": "#004385" // <-- colour is looking for a value "#004385"
/* -----------------------------------------------------------------------
ā¢ here, the color object is looking for a colour value called "_barColour"
ā¢ no such colour exists in the rainbow or the list of CSS colour names
----------------------------------------------------------------------- */
"color": "_barColour", // <-- colour is looking for a value "_barColour"
/* -----------------------------------------------------------------------
ā¢ here, the color object contains an expression ("expr":)
ā¢ this enables Vega-Lite to look for a parameter called "_barColour"
ā¢ the parameter contains the colour value "#004385"
----------------------------------------------------------------------- */
"color":{"expr": "_barColour" }, // <-- colour is looking for a parameter "_barColour"
...}
}
],
"encoding": {
"x": {...},
"y": {...}
}
}
So when we run the amended code, we get success!
{
"data": {"name": "dataset"},
"params": [
{
"name": "_barColour", //<-- magic recipe
"value": "#004385"
}
],
"transform": [],
"layer": [
{
"mark": {
"type": "bar",
"width": {"band": 0.65},
"xOffset": 0,
"color":{"expr": "_barColour" }, //<<-- magic recipe
"stroke": "black",
"strokeWidth": 1
}
}
],
"encoding": {
"x": {
"field": "Date",
"type": "ordinal",
"timeUnit": "yearmonth",
"axis": {
"title": null,
"format": "%b-%y"
}
},
"y": {
"field": "AC",
"type": "quantitative",
"axis": {
"format": "Ā£0,,.#M",
"formatType": "pbiFormat"
}
}
}
}
Letās try it again, puting the parameters in the Config tab.
Option 2: Config
Copy and paste the parameter including the curly braces and place it in the Config section, at the end of our parameters array (between the square brackets).
// copy this bit
{
"name": "_barColour",
"value": "#004385"
}
and paste it here:
{
"view": {"stroke": "transparent"},
"params": [ //<<--- open parameters array
{
"name": "globalFont",
"value": "segoe ui"
},
{
"name": "globalFontAxis",
"value": "segoe ui semibold"
},
//------------------------------------------ copy and paste
{
"name": "_barColour", //<-- paste
"value": "#004385" //<-- paste
}
//------------------------------------------ copy and paste
], //<<--- close parameters array
"axis": {
"labelFont": {
"expr": "globalFontAxis"
},
"labelFontSize": 16,
"titleFont": {"expr": "globalFont"},
"title": null,
"tickCount": 4,
"ticks": false,
"domain": false,
"gridDash": [2, 4],
"gridOpacity": 0.4
},
"axisX": {
"ticks": true,
"domain": true
},
"axisY": {
"labelPadding": 18,
"ticks": false,
"domain": false
},
"mark": {
"font": {"expr": "globalFont"}
}
}
And run your new spec:
{
"data": {"name": "dataset"},
"params": [
//--- empty / no parameters, it's living in the Config tab now ---\\
],
"transform": [],
"layer": [
{
"mark": {
"type": "bar",
"width": {"band": 0.65},
"xOffset": 0,
"color":{"expr": "_barColour" }, //<-- magic recipe
"stroke": "black",
"strokeWidth": 1
}
}
],
"encoding": {
"x": {
"field": "Date",
"type": "ordinal",
"timeUnit": "yearmonth",
"axis": {
"title": null,
"format": "%b-%y"
}
},
"y": {
"field": "AC",
"type": "quantitative",
"axis": {
"format": "Ā£0,,.#M",
"formatType": "pbiFormat"
}
}
}
}
So our Spec is less cluttered, and youāll see this works perfectly the same.
Time for a break? š„¹ā
Step 3: Conditional Formatting
Welcome back š„°ā¦ at this stage, Iād say we are pretty comfortable with how parameters? For our next challenge, weāll tackle the juicy bit ā Conditional Formatting!
Some remindersā¦ we are using the following fields in our dataset
The field [isYTD_INT] (is the [Date] within the Year to Date, as Int64.Type integer) is a column added to our DIM_Calendar table, with 1s and 0s
This is nice and weāll use this to our advantage.
First we want to create two parameters, one colour for the Actual (ACT), and another colour for the Forecast (FRC). So weāll amend our parameters to make this distinction clear.
Head back to the Config tab and add two more parameters:
- _barColourACT
- _barColourFRC
"params": [
{
"name": "globalFont",
"value": "segoe ui"
},
{
"name": "globalFontAxis",
"value": "segoe ui semibold"
},
{
"name": "_barColour",
"value": "#004385"
},
{
"name": "_barColourACT", //<-- new parameters (Actual colour)
"value": "#004385"
},
{
"name": "_barColourFRC", //<-- new parameters (Forecast colour)
"value": "whitesmoke" //<-- Hex code #F5F5F5
}
]
Excellent. Now stay with me, weāll be doing a bit of back and forth comparisons, you might feel dizzy, but I want to make sure that we understand the concepts we are familiar with and how they compare in the VL context.
We are going to look at the DAX conditional formatting measure and recreate the same effect using a VL expression.
In Vega-Lite, we have several options. Letās look at them individually, them put them into context:
Traditional IF Statement
(datum = single data point / data value)
// traditional IF statement
"color": {
"expr": "if(datum.isYTD_INT == 1 , _barColourACT , _barColourFRC)"
}
/* plain english: if the value in the [isYTD_INT] field equals 1
then return _barColourACT otherwise return _barColourFRC */
Ternary Operators (kinda like shortcuts)
// ternary operators
"color": {
"expr": "datum.isYTD_INT == 1 ? _barColourACT : _barColourFRC"
}
/*
ā¢ "?" = if the logical test true then return subsequent value...
ā¢ ":" = otherwise return subsequent value
ā¢ eg: "logical test ? return value : return another Value"
plain english: if the value in the [isYTD_INT] field equals 1
then return _barColourACT otherwise return _barColourFRC */
We can see what dataset looks like in the Deneb Editor:
This is amazing, and when you are able to break it down into digestible chunks šŖ, itās much easer to ā¦. digest š
But what if I donāt want to use a Power Query helper column? What if I want all my ācalculatedā columns to be derived from the Vega-Lite code itself? Donāt worryā¦ I gotchu!
2. Conditional Formatting with in-line Vega-Lite expressions
The next section might seem a bit daunting, but if you can create DAX measures or add Power Query columns, you can definitely create Vega-Lite expressions using calculate transforms (transformations).
The idea of a calculate transform, is very similar to that of a calculated column, a power query column or a measure. We are deriving new data inside of the Vega-Lite dataset which we can then reference for our conditional formatting expressions.
The documentation gives us a framework to build calculate tranfroms:
Letās put this into practice. We want to recreate and replace the [isYTD_INT] field with a Vega-Lite [_isYTD] calculated field.
Step 4: Create the calculate transforms
First things first, we want to find out what TODAY() is, and flag a ā1ā for anything that is on or earlier than TODAY().
This is what TODAY() looks like in Power Query:
// Power Query
Date.From( DateTime.FixedLocalNow() )
Then to create the flag column, we complete the code:
Table.AddColumn(
#"Inserted Multiplication",
"isYTD_INT",
each
let
_today = Date.From(DateTime.FixedLocalNow()), // today variable
_result = if [Date] <= _today then 1 else 0 // calculation
in
_result,
Int64.Type
)
In Vega-Lite we can do something very similar, first we want to add a transform array object to hold our array of calculate transforms:
"transform": [
{
"calculate": "datetime( now()) ", // transform NOW() into Date format
"as": "_Today" // name this field "_Today"
},
{
// if the [Date] field is <= the field value _Today then 1 else 0
"calculate": "datum['Date'] <= datum._Today ? 1 : 0 ",
"as": "_isYTD"
}
]
And putting it into context, here is our Spec:
{
"data": {"name": "dataset"},
"params": [],
"transform": [
// date today() transform
{
"calculate": "datetime( now()) ",
"as": "_Today"
},
// isYTD transform
{
"calculate": "datum['Date'] <= datum._Today ? 1 : 0 ",
"as": "_isYTD"
}
],
"layer": [
{
"mark": {
"type": "bar",
"width": {"band": 0.65},
"xOffset": 0,
/* ---------------------------------------------------------------
if the datum in our calculated tranform field "_isYTD" is 1
then return our _barColourACT parameter, otherwise _barColourFRC
----------------------------------------------------------------*/
"color": {
"expr": "datum._isYTD == 1 ? _barColourACT : _barColourFRC"
},
//----------------------------------------------------------------
"stroke": "black",
"strokeWidth": 1
}
}
],
"encoding": {
"x": {
"field": "Date",
"type": "ordinal",
"timeUnit": "yearmonth",
"axis": {
"title": null,
"format": "%b-%y"
}
},
"y": {
"field": "AC",
"type": "quantitative",
"axis": {
"format": "Ā£0,,.#M",
"formatType": "pbiFormat"
}
}
}
}
We can also check how these calculate transform behave within the VL dataset in the Editorās data viewer:
Officially MINDBLOWN š¤Æš¤Ŗ!
Iāve broken down all these steps AND MORE in the PBIX file. I had intended to write more, but I think this is a sensible place to pause for today š
šš½. So Iāll call this article Part 1. In Part 2 next time, Iāll look at more conditional formatting techniques using the Condition expression in the Encoding channel.
I hope you find the article useful, thank you for following me on this journey ā until next timeā¦ #StayQueryous
šGithub link to PBIX: EP06 Deneb VegaLite Series.pbix