# Conditional colouring using kibana canvas expression editor

**URL:** <https://discuss.elastic.co/t/conditional-colouring-using-kibana-canvas-expression-editor/177929>\
**Category:** Kibana\
**Tags:** canvas\
**Created:** [April 23, 2019, 3:42am UTC](https://discuss.elastic.co/t/conditional-colouring-using-kibana-canvas-expression-editor/177929 "2019-04-23T03:42:57Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![ksunil](https://avatars.discourse-cdn.com/v4/letter/k/439d5e/32.png) [@ksunil](https://discuss.elastic.co/u/ksunil)\
**Post date:** [April 23, 2019, 3:42am UTC](https://discuss.elastic.co/t/conditional-colouring-using-kibana-canvas-expression-editor/177929/1 "2019-04-23T03:42:57Z")

</div>

Hi,

I have an metric element called "Memory%", now would like to display the metric value in different colours based on the value. If the value is greater than 75% and less than 90% want to display the value in amber colour and if it is greater than 90% want to display in red colour.  
Need a help on this please

---

<div class="post-metadata">

**Author:** ![bhavyarm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/bhavyarm/32/22392_2.png) [@bhavyarm](https://discuss.elastic.co/u/bhavyarm)\
**Post date:** [April 25, 2019, 1:33am UTC](https://discuss.elastic.co/t/conditional-colouring-using-kibana-canvas-expression-editor/177929/2 "2019-04-25T01:33:38Z")

</div>

@alexf can we please get some help here? I know we definitely can change it. But I am failing at getting examples and docs to help the user here.

Thanks,  
Bhavya

---

<div class="post-metadata">

**Author:** ![ksunil](https://avatars.discourse-cdn.com/v4/letter/k/439d5e/32.png) [@ksunil](https://discuss.elastic.co/u/ksunil)\
**Post date:** [April 26, 2019, 9:37am UTC](https://discuss.elastic.co/t/conditional-colouring-using-kibana-canvas-expression-editor/177929/3 "2019-04-26T09:37:14Z")

</div>

Below is the query from expression editor in canvas dashboard

filters  
| essql  
query="SELECT "system.load.1" AS LOAD FROM "metricbeat\*" WHERE metricset.module = 'system' AND metricset.name = 'load' AND beat.hostname LIKE 'XXXXXX%' AND "@timestamp" \<= NOW () LIMIT 1"  
|metric Load  
|getCell "LOAD"  
|if {lte 10}  
then={markdown "\*\*" font={font family="'Open Sans', Helvetica, Arial, sans-serif" size=14 align="left" color="#dd0a73" weight="bold" underline=false italic=false}}  
| render

Getting error "Expression failed with [math] \> Can not cast 'render' to any of 'number, datatable' "

---

<div class="post-metadata">

**Author:** ![ksunil](https://avatars.discourse-cdn.com/v4/letter/k/439d5e/32.png) [@ksunil](https://discuss.elastic.co/u/ksunil)\
**Post date:** [May 2, 2019, 2:27am UTC](https://discuss.elastic.co/t/conditional-colouring-using-kibana-canvas-expression-editor/177929/4 "2019-05-02T02:27:59Z")

</div>

Hi,

Can anyone help me on this

---

<div class="post-metadata">

**Author:** ![Catherine\_Liu](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/catherine_liu/32/34294_2.png) [@Catherine\_Liu](https://discuss.elastic.co/u/Catherine_Liu)\
**Post date:** [May 9, 2019, 7:48pm UTC](https://discuss.elastic.co/t/conditional-colouring-using-kibana-canvas-expression-editor/177929/5 "2019-05-09T19:48:24Z")

</div>

> [@ksunil](#):
>
> filters  
> | essql  
> query="SELECT "system.load.1" AS LOAD FROM "metricbeat\*" WHERE metricset.module = 'system' AND metricset.name = 'load' AND beat.hostname LIKE 'XXXXXX%' AND "@timestamp" \<= NOW () LIMIT 1"

Try this expression and see if the element looks right:

```auto
filters
| essql
query="SELECT "system.load.1" AS LOAD FROM "metricbeat*" WHERE metricset.module = 'system' AND metricset.name = 'load' AND beat.hostname LIKE 'XXXXXX%' AND "@timestamp" <= NOW () LIMIT 1"
| metric LOAD 
  metricFont={font size=48 family="'Open Sans', Helvetica, Arial, sans-serif" color={if {lt 10} then="red" else="green"} align="center" lHeight=48} 
  labelFont={font size=14 family="'Open Sans', Helvetica, Arial, sans-serif" color="#000000" align="center"}
| render

```

---

<div class="post-metadata">

**Author:** ![Catherine\_Liu](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/catherine_liu/32/34294_2.png) [@Catherine\_Liu](https://discuss.elastic.co/u/Catherine_Liu)\
**Post date:** [May 9, 2019, 8:11pm UTC](https://discuss.elastic.co/t/conditional-colouring-using-kibana-canvas-expression-editor/177929/6 "2019-05-09T20:11:32Z")

</div>

@ksunil Actually try this expression instead:

```auto
filters
| essql 
  query="SELECT \"system.load.1\" AS LOAD FROM \"metricbeat*\" WHERE metricset.module = 'system' AND metricset.name = 'load' AND beat.hostname LIKE 'XXXXXX%' AND \"@timestamp\" <= NOW () LIMIT 1"
| math "LOAD"
| metric "Load" 
  metricFont={font size=48 family="'Open Sans', Helvetica, Arial, sans-serif" color={switch case={case if={all {gt 0.75} {lt 0.9}} then="yellow"} case={case if={gte 0.9} then="red"} default="green"} align="center" lHeight=48} 
  labelFont={font size=14 family="'Open Sans', Helvetica, Arial, sans-serif" color="#000000" align="center"}
| render

```

---

<div class="post-metadata">

**Author:** ![MarkusP](https://avatars.discourse-cdn.com/v4/letter/m/e19adc/32.png) [@MarkusP](https://discuss.elastic.co/u/MarkusP)\
**Post date:** [May 10, 2019, 9:19am UTC](https://discuss.elastic.co/t/conditional-colouring-using-kibana-canvas-expression-editor/177929/7 "2019-05-10T09:19:20Z")

</div>

@Catherine_Liu I have basically the same problem not with the font color but with the background color

I have just one condition instead of two my approach is quite like the fist you gave to ksunil.

I do a select count(\*) and if it is greater than 0 the backround should be green otherwise it should be red.

The expression editor looks like below

`filters | essql query="SELECT * FROM uacuser WHERE logintime > NOW() - INTERVAL 1 MONTH AND disabled = 0" | math "size(userid)" | metric "Users logged in this month" metricFont={font family="'Open Sans', Helvetica, Arial, sans-serif" size=48 align="center" color="#FFFFFF" weight="normal" underline=false italic=false} labelFont={font family="'Open Sans', Helvetica, Arial, sans-serif" size=14 align="center" color="#FFFFFF" weight="normal" underline=false italic=false} | render **containerStyle={containerStyle backgroundColor={if {gt 0} then="red" else="green"}}** ` // this  
is the line that matters

It seems that the condition works not like intended , it is always else and showing a green background. Did I do something wrong? Is the condition always related to the value in the metric? Or is it possibly a bug?  
P.S How can you do that code snipet in a post?

---

<div class="post-metadata">

**Author:** ![ksunil](https://avatars.discourse-cdn.com/v4/letter/k/439d5e/32.png) [@ksunil](https://discuss.elastic.co/u/ksunil)\
**Post date:** [May 13, 2019, 3:14am UTC](https://discuss.elastic.co/t/conditional-colouring-using-kibana-canvas-expression-editor/177929/8 "2019-05-13T03:14:05Z")

</div>

Hi,

Thanks Catherine Liu, now able to display the metric font in colours based on the condition.  
Thanks, it helped and works for me

---

<div class="post-metadata">

**Author:** ![ksunil](https://avatars.discourse-cdn.com/v4/letter/k/439d5e/32.png) [@ksunil](https://discuss.elastic.co/u/ksunil)\
**Post date:** [May 14, 2019, 6:13am UTC](https://discuss.elastic.co/t/conditional-colouring-using-kibana-canvas-expression-editor/177929/9 "2019-05-14T06:13:34Z")

</div>

Hi Catherine,

When i user FormatNumber "0.0a" for metric ,coloring of metric is not working though colors will change based on metric value.  
Please help me regarding this

---

<div class="post-metadata">

**Author:** ![Gael](https://avatars.discourse-cdn.com/v4/letter/g/a698b9/32.png) [@Gael](https://discuss.elastic.co/u/Gael)\
**Post date:** [May 20, 2019, 8:19am UTC](https://discuss.elastic.co/t/conditional-colouring-using-kibana-canvas-expression-editor/177929/10 "2019-05-20T08:19:49Z")

</div>

> [@MarkusP](#):
>
> @Catherine_Liu I have basically the same problem not with the font color but with the background color
> 
> I have just one condition instead of two my approach is quite like the fist you gave to ksunil.
> 
> I do a select count(\*) and if it is greater than 0 the backround should be green otherwise it should be red.
> 
> The expression editor looks like below
> 
> `filters | essql query="SELECT * FROM uacuser WHERE logintime > NOW() - INTERVAL 1 MONTH AND disabled = 0" | math "size(userid)" | metric "Users logged in this month" metricFont={font family="'Open Sans', Helvetica, Arial, sans-serif" size=48 align="center" color="#FFFFFF" weight="normal" underline=false italic=false} labelFont={font family="'Open Sans', Helvetica, Arial, sans-serif" size=14 align="center" color="#FFFFFF" weight="normal" underline=false italic=false} | render **containerStyle={containerStyle backgroundColor={if {gt 0} then="red" else="green"}}** ` // this  
> is the line that matters
> 
> It seems that the condition works not like intended , it is always else and showing a green background. Did I do something wrong? Is the condition always related to the value in the metric? Or is it possibly a bug?  
> P.S How can you do that code snipet in a post?

I have the same issue. But if I remove the following part, I obtain a conditionnal background without being able to modify the metric parameters.  
`| metric "Users logged in this month" metricFont={font family="'Open Sans', Helvetica, Arial, sans-serif" size=48 align="center" color="#FFFFFF" weight="normal" underline=false italic=false} labelFont={font family="'Open Sans', Helvetica, Arial, sans-serif" size=14 align="center" color="#FFFFFF" weight="normal" underline=false italic=false}`  
This is a strange behaviour.  
Here is what I what I use to have this "strange" conditionnal background:  
`filters | essql query="SELECT * FROM uacuser WHERE logintime > NOW() - INTERVAL 1 MONTH AND disabled = 0" | math "size(userid)" | render **containerStyle={containerStyle backgroundColor={if {gt 0} then="red" else="green"}}`

---

<div class="post-metadata">

**Author:** ![Cosma\_Silviu](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/cosma_silviu/32/41936_2.png) [@Cosma\_Silviu](https://discuss.elastic.co/u/Cosma_Silviu)\
**Post date:** [May 30, 2019, 12:52pm UTC](https://discuss.elastic.co/t/conditional-colouring-using-kibana-canvas-expression-editor/177929/11 "2019-05-30T12:52:02Z")

</div>

Hi I am trying to change the colour of the service\_availability based on the procentage. If 99.5 or higher will be green, if is lower will be red.  
The problem is the conditional is not working, it's taking the default value. Any idea how I can fix it? Please find the code bellow

```
filters

```

| essql  
query="SELECT "@timestamp", env, ROUND((100 -(sla.timeout + 0.0) / (sla.searches + 0.0) \* 100.0),1) as service\_availability from "service\*" where sla.searches is not null and env='parpr1' order by "@timestamp" desc LIMIT 1"  
| markdown "  
{{#each rows}}  
**{{service\_availability}}%**  
{{/each}}

"  
font={font family="'Open Sans', Helvetica, Arial, sans-serif" size=24 align="center" color={switch case={case if={gte 99.5} then="green"} case={case if={lt 99.5} then="red"} default="red"} weight="normal" underline=false italic=false}  
| render

---

<div class="post-metadata">

**Author:** ![Catherine\_Liu](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/catherine_liu/32/34294_2.png) [@Catherine\_Liu](https://discuss.elastic.co/u/Catherine_Liu)\
**Post date:** [May 31, 2019, 8:10pm UTC](https://discuss.elastic.co/t/conditional-colouring-using-kibana-canvas-expression-editor/177929/12 "2019-05-31T20:10:18Z")

</div>

> [@Catherine\_Liu](#):
>
> filters | essql query="SELECT "system.load.1" AS LOAD FROM "metricbeat\*" WHERE metricset.module = 'system' AND metricset.name = 'load' AND beat.hostname LIKE 'XXXXXX%' AND "@timestamp" \<= NOW () LIMIT 1" | math "LOAD" | metric "Load" metricFont={font size=48 family="'Open Sans', Helvetica, Arial, sans-serif" color={switch case={case if={all {gt 0.75} {lt 0.9}} then="yellow"} case={case if={gte 0.9} then="red"} default="green"} align="center" lHeight=48} labelFont={font size=14 family="'Open Sans', Helvetica, Arial, sans-serif" color="#000000" align="center"} | render

@ksunil Hmm that's odd. What does your expression look like?

Did you add it between your `math` and `metric` like this? This example should work and retain the conditional coloring.

filters  
| essql  
query="SELECT "system.load.1" AS LOAD FROM "metricbeat\*" WHERE metricset.module = 'system' AND metricset.name = 'load' AND beat.hostname LIKE 'XXXXXX%' AND "@timestamp" \<= NOW () LIMIT 1"  
| math "LOAD"  
| formatNumber format="0.0a"  
| metric "Load"  
metricFont={font size=48 family="'Open Sans', Helvetica, Arial, sans-serif" color={switch case={case if={all {gt 0.75} {lt 0.9}} then="yellow"} case={case if={gte 0.9} then="red"} default="green"} align="center" lHeight=48}  
labelFont={font size=14 family="'Open Sans', Helvetica, Arial, sans-serif" color="#000000" align="center"}  
| render

---

<div class="post-metadata">

**Author:** ![Catherine\_Liu](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/catherine_liu/32/34294_2.png) [@Catherine\_Liu](https://discuss.elastic.co/u/Catherine_Liu)\
**Post date:** [May 31, 2019, 8:22pm UTC](https://discuss.elastic.co/t/conditional-colouring-using-kibana-canvas-expression-editor/177929/13 "2019-05-31T20:22:39Z")

</div>

> [@Cosma\_Silviu](#):
>
> Hi I am trying to change the colour of the service\_availability based on the procentage. If 99.5 or higher will be green, if is lower will be red.  
> The problem is the conditional is not working, it's taking the default value. Any idea how I can fix it? Please find the code bellow
> 
> ```auto
> filters
> 
> ```
> 
> | essql  
> query="SELECT "@timestamp", env, ROUND((100 -(sla.timeout + 0.0) / (sla.searches + 0.0) \* 100.0),1) as service\_availability from "service\*" where sla.searches is not null and env='parpr1' order by "@timestamp" desc LIMIT 1"  
> | markdown "  
> {{#each rows}}  
> **{{service\_availability}}%**  
> {{/each}}
> 
> "  
> font={font family="'Open Sans', Helvetica, Arial, sans-serif" size=24 align="center" color={switch case={case if={gte 99.5} then="green"} case={case if={lt 99.5} then="red"} default="red"} weight="normal" underline=false italic=false}  
> | render

@Cosma_Silviu since you only have the two cases, you can simply use the [`if`](https://www.elastic.co/guide/en/kibana/current/canvas-common-functions.html#_if) function instead of the [`switch`](https://www.elastic.co/guide/en/kibana/current/canvas-common-functions.html#_switch) function which is used for multiple branching conditions.

The issue is your conditional subexpressions, (i.e. `{gte 99.5}`,`{lt 99.5}`) are receiving the same input or _context_ as the `metric` function they're nested within, which is the entire output of your `essql` datasource, so it's comparing the number `0.9` against an entire `datatable`. What you need to do is extract the value from the `datatable` using a function like [`getCell`](https://www.elastic.co/guide/en/kibana/current/canvas-common-functions.html#_getcell) and pipe that value into `gte 99.5`

Something like this should work:

```auto
filters
| essql 
  query="SELECT \"@timestamp\", env, ROUND((100 -(sla.timeout + 0.0) / (sla.searches + 0.0) * 100.0),1) as service_availability from \"service*\" where sla.searches is not null and env='parpr1' order by \"@timestamp\" desc LIMIT 1"
| markdown "{{#each rows}}
{{service_availability}}%
{{/each}}" 
  font={font size=14 family="'Open Sans', Helvetica, Arial, sans-serif" color={if {getCell "service_availability" | get 99.5} then="green" else="red"} align="left"}

```

---

<div class="post-metadata">

**Author:** ![Cosma\_Silviu](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/cosma_silviu/32/41936_2.png) [@Cosma\_Silviu](https://discuss.elastic.co/u/Cosma_Silviu)\
**Post date:** [June 3, 2019, 2:37pm UTC](https://discuss.elastic.co/t/conditional-colouring-using-kibana-canvas-expression-editor/177929/14 "2019-06-03T14:37:14Z")

</div>

Thank you, it worked

---

<div class="post-metadata">

**Author:** ![ScottBarnes](https://avatars.discourse-cdn.com/v4/letter/s/e0b2c6/32.png) [@ScottBarnes](https://discuss.elastic.co/u/ScottBarnes)\
**Post date:** [June 6, 2019, 10:35am UTC](https://discuss.elastic.co/t/conditional-colouring-using-kibana-canvas-expression-editor/177929/15 "2019-06-06T10:35:46Z")

</div>

> [@Catherine\_Liu](#):
>
> {if {lt 10} then="red" else="green"}

I've not been able to get the background colour to fall into the "then" outcome of an if statement in the containerStyle. Is it something to do with the current context being after the render??!?!?

I'd really like to resolve this so we can stop using DataDog for our dashboards!!!

---

<div class="post-metadata">

**Author:** ![tims](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/tims/32/46750_2.png) [@tims](https://discuss.elastic.co/u/tims)\
**Post date:** [June 13, 2019, 7:29pm UTC](https://discuss.elastic.co/t/conditional-colouring-using-kibana-canvas-expression-editor/177929/16 "2019-06-13T19:29:38Z")

</div>

Hey Scott, can you post a code snippet of what you are trying when the conditional doesn't work as expected?

---

<div class="post-metadata">

**Author:** ![sharonva](https://avatars.discourse-cdn.com/v4/letter/s/50afbb/32.png) [@sharonva](https://discuss.elastic.co/u/sharonva)\
**Post date:** [June 19, 2019, 12:30pm UTC](https://discuss.elastic.co/t/conditional-colouring-using-kibana-canvas-expression-editor/177929/17 "2019-06-19T12:30:04Z")

</div>

Hi tims,

I have the same problem as Scott , trying to change background color by condition and it doesnt work. Here is the code snipet:

filters  
| essql  
query="SELECT  
count("eai\_sts\_proc") AS cnt  
FROM  
"logit-exampleindex-\*"  
WHERE  
app\_name = 'EXAMPLEAPP'  
AND  
command = 'EXAMPLESERVICE'  
AND  
eai\_sts\_proc= 'OK'  
AND  
"@timestamp" \> NOW() - INTERVAL 5 MINUTES"  
| math "cnt"  
| metric ""  
metricFont={font family="'Open Sans', Helvetica, Arial, sans-serif" size=24 align="center" color="#000000" weight="bold" underline=false italic=false}  
labelFont={font family="'Open Sans', Helvetica, Arial, sans-serif" size=14 align="center" color="#000000" weight="normal" underline=false italic=false}  
| render  
containerStyle={containerStyle backgroundColor={if {lt 1} then="red" else="green"}}

it is always else and showing a green background  
Thanks

---

<div class="post-metadata">

**Author:** ![Catherine\_Liu](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/catherine_liu/32/34294_2.png) [@Catherine\_Liu](https://discuss.elastic.co/u/Catherine_Liu)\
**Post date:** [June 28, 2019, 9:11pm UTC](https://discuss.elastic.co/t/conditional-colouring-using-kibana-canvas-expression-editor/177929/18 "2019-06-28T21:11:30Z")

</div>

@ScottBarnes @sharonva You can't conditionally change the color of the background, because sub-expressions in the `render` function receive a `render` object as context and tries to compare your conditional expressions to the `render` object.

The simple way around this is to add a separate `shape` element that serves as a background element hooked up to the same datasource where you have access to your datasource to conditionally render the color of the `shape`.

Another solution would be to pipe the same datasource into the beginning of your conditional sub-expression.

For example:

```auto
...
| render containerStyle={
     containerStyle backgroundColor={
       filters | demodata | math "sum(price)" | if {gt 50} then="red" else="green"
     } 
  }
```

---

<div class="post-metadata">

**Author:** ![sharonva](https://avatars.discourse-cdn.com/v4/letter/s/50afbb/32.png) [@sharonva](https://discuss.elastic.co/u/sharonva)\
**Post date:** [July 7, 2019, 7:01am UTC](https://discuss.elastic.co/t/conditional-colouring-using-kibana-canvas-expression-editor/177929/19 "2019-07-07T07:01:33Z")

</div>

Thanks Catherine, It works.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [August 4, 2019, 7:01am UTC](https://discuss.elastic.co/t/conditional-colouring-using-kibana-canvas-expression-editor/177929/20 "2019-08-04T07:01:34Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.