Search code examples
powerbipowerbi-desktopvega-litevegadeneb

Deneb & PowerBI: Risk matrix Project

I am currently working on a risk matrix in PowerBI which I want to create with Deneb. The goal is a 3x3 matrix that allows cross-filtering. David already gave create support regarding the Vega lite code, however, some PowerBI specific questions remain. My current progress can be seen here: PowerBi File.

Now I face the following challenges, which I am grateful for any help in overcoming:

  • The selected cell should be highlighted (see sheet 2 as an example). As an alternative it would be nice if at least the selected number can be highlighted. With "__ selected__" I unfortunately did not reach the objective.

  • It would be nice if a 0 appeared in the empty field.

  • There should be a space between each cell comparable to the one on page 2.

As always, I appreciate any solutions, hints or ideas. :)

Solution

  • OK, follow these steps to have a fully working and interactive risk matrix like the one below. enter image description here

    enter image description here

    enter image description here

    1. Create a Damage dimension table as follows

    enter image description here

    1. Create an Exposure dimension table as follows:

    enter image description here

    1. Create two relationships to your fact table as follows.

    enter image description here

    1. Create a measure as follows

      Risk Count = COUNTROWS(Sheet1) +0

    2. Create a new Deneb visual adding the two dimensions and the measure (make sure not to aggregate)

    enter image description here

    1. Place this code inside Deneb
    {
  "$schema": "https://vega.github.io/schema/vega-lite/v5.json",
  "data": {"name": "dataset"},
  "width": 500,
  "height": 500,
  "encoding": {
    "y": {
      "field": "Exposure",
      "type": "ordinal",
      "sort": ["high", "medium", "low"]
    },
    "x": {
      "field": "Damage",
      "type": "ordinal",
      "sort": ["low", "medium", "high"],
      "axis": {"labelAngle": 0}
    }
  },
  "layer": [
    {
      "encoding": {
        "opacity": {
          "condition": {
            "test": {"field": "__selected__", "equal": "off"},
            "value": 0.3
          }
        }
      },
      "mark": {
        "type": "rect",
        "color": {
          "expr": "(datum['Exposure'] == 'high' & datum['Damage'] == 'high') || (datum['Exposure'] == 'high' & datum['Damage'] == 'medium') || (datum['Exposure'] == 'medium' & datum['Damage'] == 'high') ? 'red' : (datum['Exposure'] == 'medium' & datum['Damage'] == 'medium') || (datum['Exposure'] == 'high' & datum['Damage'] == 'low') || (datum['Exposure'] == 'low' & datum['Damage'] == 'high') ? 'orange': 'green'"
        }
      }
    },
    {
      "mark": {
        "type": "text",
        "fontSize": 16,
        "fontWeight": "bold",
        "color": "white"
      },
      "encoding": {"text": {"field": "Risk Count", "type": "quantitative"}}
    }
  ],
  "config": {"axis": {"grid": true, "tickBand": "extent"}}
}