Trelli/multiple scatter deneb

[JimmyCruyp]

Hello guys,

I need to create a multiple/treilli scatter. But i dont see the possibility to do it in Power BI. and i dont see it on the store. Maybe someone could support by creating the graph in deneb module?

Thanks a lot guys,

Jim

[giammariam · Accepted solution]

Hey @JimmyCruyp. Here's my implementation of the SPLOM (scatterplot matrix). Your dataset will need to be flattened and the structure will consist of the following 4 fields:

• Index - a sequential integer identifier for the row 
• Observation - The idenitifying name for each dot in the matrix
• Attribute - The variable that will show up on the x and y axes
• Value - The value for the attribute for the given observation

• .pbix
• gist
• spec:

{
  "$schema": "https://vega.github.io/schema/vega-lite/v5.json",
  "params": [{"name": "observation", "expr": "toString(brush['Observation'])"}],
  "data": {
    "name": "dataset",
    "url": "https://raw.githubusercontent.com/Giammaria/PublicFiles/master/pbi/pbi_community_help/20230309_trellis_scatter/data/mtcars_unpivoted.tsv",
    "format": {"parse": {"Index": "number", "Value": "number"}}
  },
  "transform": [
    {"joinaggregate": [{"op": "count", "as": "total_observation_count"}]},
    {
      "calculate": "sequence(1,datum['total_observation_count']+1,1)",
      "as": "index_2"
    },
    {
      "calculate": "split(replace(replace(join(datum['index_2'], ','), toString(datum['Index']), ''), ',,', ','), ',')",
      "as": "index_2"
    },
    {"flatten": ["index_2"], "as": ["index_2"]},
    {"filter": "datum['index_2']"},
    {"calculate": "+datum['index_2']", "as": "index_2"},
    {
      "lookup": "index_2",
      "from": {
        "data": {"name": "dataset"},
        "key": "index",
        "fields": ["Attribute", "Value"]
      },
      "as": ["attribute_2", "value_2"]
    },
    {
      "calculate": "data('dataset')[datum['index_2']-1]",
      "as": "row_to_compare"
    },
    {"filter": "datum['Observation']===datum['row_to_compare']['Observation']"},
    {"calculate": "datum['row_to_compare']['Attribute']", "as": "attribute_2"},
    {"calculate": "datum['row_to_compare']['Value']", "as": "value_2"},
    {
      "calculate": "[datum['Attribute'], datum['attribute_2']]",
      "as": "variables"
    },
    {
      "window": [{"op": "distinct", "field": "variables", "as": "facet_count"}],
      "frame": [null, null],
      "sort": [{"field": "facet_index", "order": "ascending"}]
    },
    {
      "window": [
        {"op": "dense_rank", "field": "variables", "as": "facet_index"}
      ],
      "sort": [{"field": "variables", "order": "ascending"}],
      "frame": [null, null]
    },
    {"calculate": "floor(sqrt(datum['facet_count']))", "as": "row_count"},
    {
      "calculate": "ceil(datum['facet_count']/datum['row_count'])",
      "as": "column_count"
    },
    {
      "calculate": "datum['facet_index']%datum['column_count'] === 0 ? datum['column_count'] : datum['facet_index']%datum['column_count']",
      "as": "column"
    },
    {
      "window": [{"op": "dense_rank", "as": "row"}],
      "groupby": ["column"],
      "frame": [null, 1],
      "sort": [{"field": "facet_index", "order": "ascending"}]
    },
    {
      "window": [{"op": "row_number", "as": "facet_data_index"}],
      "groupby": ["facet_index"],
      "frame": [null, null]
    },
    {
      "calculate": "datum['Attribute']+': '+toString(datum['Value'])",
      "as": "tooltipX"
    },
    {
      "calculate": "datum['attribute_2']+': '+toString(datum['value_2'])",
      "as": "tooltipY"
    }
  ],
  "facet": {
    "row": {"field": "row", "title": null, "header": null, "type": "ordinal"},
    "column": {
      "field": "column",
      "type": "ordinal",
      "title": null,
      "header": null
    }
  },
  "spec": {
    "width": 150,
    "height": 150,
    "layer": [
      {
        "name": "splom",
        "params": [
          {
            "name": "brush",
            "select": {
              "type": "point",
              "on": "mouseover",
              "encodings": ["color"],
              "nearest": true,
              "clear": {
                "type": "mouseout"
              }
            }
          }
        ],
        "mark": {"type": "point", "filled": true},
        "encoding": {
          "x": {
            "field": "Value",
            "type": "quantitative",
            "axis": {"title": null}
          },
          "y": {"field": "value_2", "type": "quantitative", "title": null},
          "color": {
            "condition": {
              "test": "observation===datum['Observation']",
              "field": "Observation",
              "type": "nominal",
              "legend": null
            },
            "value": "steelblue"
          },
          "size": {
            "condition": {
              "test": "observation===datum['Observation']",
              "value": 400
            },
            "value": 40
          },
          "tooltip": [
            {"title": "Observation", "field": "Observation"},
            {"title": "x", "field": "tooltipX"},
            {"title": "y", "field": "tooltipY"}
          ]
        }
      },
      {
        "name": "x-title",
        "transform": [{"filter": "datum['facet_data_index']===1"}],
        "mark": {
          "type": "text",
          "y": {"expr": "child_height"},
          "yOffset": 25,
          "fontSize": 14
        },
        "encoding": {"text": {"field": "Attribute"}}
      },
      {
        "name": "y-title",
        "transform": [{"filter": "datum['facet_data_index']===1"}],
        "mark": {
          "type": "text",
          "align": "center",
          "angle": 270,
          "x": {"expr": "0"},
          "xOffset": -30,
          "fontSize": 14
        },
        "encoding": {"text": {"field": "attribute_2"}}
      }
    ]
  },
  "resolve": {
    "axis": {"x": "independent", "y": "independent"},
    "scale": {"x": "independent", "y": "independent"}
  },
  "config": {"range": {"category": ["orange"]}}
}​

[giammariam]

Near the end of this article shows how to change that setting.

[giammariam]

Hey Jim,

This is a limitation of faceting in vega-lite; currently the facet cell size cannot dynamically change based on the size of the container (yet).

I (and many others) really wish they'd add this responsive capability for faceted (i.e. trellis) charts, as well as other charts that have layout composition (e.g. concatenated charts). 

See these links for more info

• https://github.com/deneb-viz/deneb/issues/203
• https://github.com/vega/vega-lite/issues/5219

Without knowing the maximum amount of charts your data will generate, as well as not knowing the formatting property dimensions (width and height) of the Deneb visualization container will be ahead of time, I can't properly set the width and height values in the spec:{} section of the overall Vega-Lite spec ahead of time. This is something you could do via trial and error before handing off the report to your users.

I wish there was a better answer because this is certainly a limitation that many folks are experiencing.

[JimmyCruyp]

could we imagine creating a small additional module or in the left part of the framework, which would allow an inexperienced user to modify, adjust the height and the width of the graphs? thanks again

[giammariam]

This may be possible with input parameters. For example, I may be able to add a slider at the top that will allow the user to adjust the width and height of the facet cells. I will have to play around with the idea a bit later today when I have time and see if it's possible.

[giammariam · Accepted solution]

Hey Jim. This is far from an idea solution but it's as close as I was able to get given the current limitations. Unfortunately we do not currently have control as to where the input elements (in this case, a slider) can be placed). I also had to switch from vega-lite to vega to implement this, so you'll want to make sure that the spec is set to vega in Deneb:

Ideally with your real dataset, you'd have fewer variables (attributes) and therefore, fewer charts in your SPLOM. 

If you need to change the initial size of your cells (which you probably should), you'll want to update this value:

• .pbix
• gist
• spec

[JimmyCruyp]

This is perfect. thanks a lot. The only point is maybe if you can just remove the requirement to press control to select certain attributes .... 

[giammariam]

Hey again @JimmyCruyp. Happy to help. Do you have a dummy dataset in mind? If not, I can generate one, you'll just need to make some updates to use the spec with your data.

[JimmyCruyp]

No I dont. Yes if you could generate one this will be great 😊

[giammariam · Accepted solution]

Hey @JimmyCruyp. Here's my implementation of the SPLOM (scatterplot matrix). Your dataset will need to be flattened and the structure will consist of the following 4 fields:

• Index - a sequential integer identifier for the row 
• Observation - The idenitifying name for each dot in the matrix
• Attribute - The variable that will show up on the x and y axes
• Value - The value for the attribute for the given observation

• .pbix
• gist
• spec:

{
  "$schema": "https://vega.github.io/schema/vega-lite/v5.json",
  "params": [{"name": "observation", "expr": "toString(brush['Observation'])"}],
  "data": {
    "name": "dataset",
    "url": "https://raw.githubusercontent.com/Giammaria/PublicFiles/master/pbi/pbi_community_help/20230309_trellis_scatter/data/mtcars_unpivoted.tsv",
    "format": {"parse": {"Index": "number", "Value": "number"}}
  },
  "transform": [
    {"joinaggregate": [{"op": "count", "as": "total_observation_count"}]},
    {
      "calculate": "sequence(1,datum['total_observation_count']+1,1)",
      "as": "index_2"
    },
    {
      "calculate": "split(replace(replace(join(datum['index_2'], ','), toString(datum['Index']), ''), ',,', ','), ',')",
      "as": "index_2"
    },
    {"flatten": ["index_2"], "as": ["index_2"]},
    {"filter": "datum['index_2']"},
    {"calculate": "+datum['index_2']", "as": "index_2"},
    {
      "lookup": "index_2",
      "from": {
        "data": {"name": "dataset"},
        "key": "index",
        "fields": ["Attribute", "Value"]
      },
      "as": ["attribute_2", "value_2"]
    },
    {
      "calculate": "data('dataset')[datum['index_2']-1]",
      "as": "row_to_compare"
    },
    {"filter": "datum['Observation']===datum['row_to_compare']['Observation']"},
    {"calculate": "datum['row_to_compare']['Attribute']", "as": "attribute_2"},
    {"calculate": "datum['row_to_compare']['Value']", "as": "value_2"},
    {
      "calculate": "[datum['Attribute'], datum['attribute_2']]",
      "as": "variables"
    },
    {
      "window": [{"op": "distinct", "field": "variables", "as": "facet_count"}],
      "frame": [null, null],
      "sort": [{"field": "facet_index", "order": "ascending"}]
    },
    {
      "window": [
        {"op": "dense_rank", "field": "variables", "as": "facet_index"}
      ],
      "sort": [{"field": "variables", "order": "ascending"}],
      "frame": [null, null]
    },
    {"calculate": "floor(sqrt(datum['facet_count']))", "as": "row_count"},
    {
      "calculate": "ceil(datum['facet_count']/datum['row_count'])",
      "as": "column_count"
    },
    {
      "calculate": "datum['facet_index']%datum['column_count'] === 0 ? datum['column_count'] : datum['facet_index']%datum['column_count']",
      "as": "column"
    },
    {
      "window": [{"op": "dense_rank", "as": "row"}],
      "groupby": ["column"],
      "frame": [null, 1],
      "sort": [{"field": "facet_index", "order": "ascending"}]
    },
    {
      "window": [{"op": "row_number", "as": "facet_data_index"}],
      "groupby": ["facet_index"],
      "frame": [null, null]
    },
    {
      "calculate": "datum['Attribute']+': '+toString(datum['Value'])",
      "as": "tooltipX"
    },
    {
      "calculate": "datum['attribute_2']+': '+toString(datum['value_2'])",
      "as": "tooltipY"
    }
  ],
  "facet": {
    "row": {"field": "row", "title": null, "header": null, "type": "ordinal"},
    "column": {
      "field": "column",
      "type": "ordinal",
      "title": null,
      "header": null
    }
  },
  "spec": {
    "width": 150,
    "height": 150,
    "layer": [
      {
        "name": "splom",
        "params": [
          {
            "name": "brush",
            "select": {
              "type": "point",
              "on": "mouseover",
              "encodings": ["color"],
              "nearest": true,
              "clear": {
                "type": "mouseout"
              }
            }
          }
        ],
        "mark": {"type": "point", "filled": true},
        "encoding": {
          "x": {
            "field": "Value",
            "type": "quantitative",
            "axis": {"title": null}
          },
          "y": {"field": "value_2", "type": "quantitative", "title": null},
          "color": {
            "condition": {
              "test": "observation===datum['Observation']",
              "field": "Observation",
              "type": "nominal",
              "legend": null
            },
            "value": "steelblue"
          },
          "size": {
            "condition": {
              "test": "observation===datum['Observation']",
              "value": 400
            },
            "value": 40
          },
          "tooltip": [
            {"title": "Observation", "field": "Observation"},
            {"title": "x", "field": "tooltipX"},
            {"title": "y", "field": "tooltipY"}
          ]
        }
      },
      {
        "name": "x-title",
        "transform": [{"filter": "datum['facet_data_index']===1"}],
        "mark": {
          "type": "text",
          "y": {"expr": "child_height"},
          "yOffset": 25,
          "fontSize": 14
        },
        "encoding": {"text": {"field": "Attribute"}}
      },
      {
        "name": "y-title",
        "transform": [{"filter": "datum['facet_data_index']===1"}],
        "mark": {
          "type": "text",
          "align": "center",
          "angle": 270,
          "x": {"expr": "0"},
          "xOffset": -30,
          "fontSize": 14
        },
        "encoding": {"text": {"field": "attribute_2"}}
      }
    ]
  },
  "resolve": {
    "axis": {"x": "independent", "y": "independent"},
    "scale": {"x": "independent", "y": "independent"}
  },
  "config": {"range": {"category": ["orange"]}}
}​

[JimmyCruyp]

I have only one concern is the size of the graphs and the fact that we can scroll. I can't ask users to change vega lite code. Could you find a way to have everything without scrolling on the same page, in the same frame and the heights and widths updating automatically? maybe this type of code?  : I tried without result... i dont program in fact...

"spec":  "width": "container", "height": "container",

"autosize": {"type": "fit", "contains": "padding"}

[JimmyCruyp]

Thanks a lot,

I sent you a lynkdin request...

Jim