Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JimmyCruyp
Helper II
Helper II

Trelli/multiple scatter deneb

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?Capture d’écran 2023-03-09 094754.png

Thanks a lot guys,

Jim

2 ACCEPTED SOLUTIONS

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

giphy

 

{
  "$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"]}}
}​

 



Madison Giammaria
Proud to be a Super User 😄
LinkedIn

Do you frequently use Deneb to provide insights to your stakeholders? Have you considered sponsoring this free and open source custom visual? More info here!

View solution in original post

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:

giammariam_2-1678499785078.png


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:

giammariam_1-1678499447937.png

 

 

giammariam_0-1678499053342.gif


Madison Giammaria
Proud to be a Super User 😄
LinkedIn

Do you frequently use Deneb to provide insights to your stakeholders? Have you considered sponsoring this free and open source custom visual? More info here!

View solution in original post

11 REPLIES 11
giammariam
Super User
Super User

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



Madison Giammaria
Proud to be a Super User 😄
LinkedIn

Do you frequently use Deneb to provide insights to your stakeholders? Have you considered sponsoring this free and open source custom visual? More info here!
giammariam
Super User
Super User

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

 

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.



Madison Giammaria
Proud to be a Super User 😄
LinkedIn

Do you frequently use Deneb to provide insights to your stakeholders? Have you considered sponsoring this free and open source custom visual? More info here!

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

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.



Madison Giammaria
Proud to be a Super User 😄
LinkedIn

Do you frequently use Deneb to provide insights to your stakeholders? Have you considered sponsoring this free and open source custom visual? More info here!

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:

giammariam_2-1678499785078.png


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:

giammariam_1-1678499447937.png

 

 

giammariam_0-1678499053342.gif


Madison Giammaria
Proud to be a Super User 😄
LinkedIn

Do you frequently use Deneb to provide insights to your stakeholders? Have you considered sponsoring this free and open source custom visual? More info here!

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
Super User
Super User

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.



Madison Giammaria
Proud to be a Super User 😄
LinkedIn

Do you frequently use Deneb to provide insights to your stakeholders? Have you considered sponsoring this free and open source custom visual? More info here!

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

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

giphy

 

{
  "$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"]}}
}​

 



Madison Giammaria
Proud to be a Super User 😄
LinkedIn

Do you frequently use Deneb to provide insights to your stakeholders? Have you considered sponsoring this free and open source custom visual? More info here!

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"}

Thanks a lot,

I sent you a lynkdin request...

Jim

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors