topic Re: Trelli/multiple scatter deneb in Custom Visuals Development Discussion

Trelli/multiple scatter deneb

JimmyCruyp — Thu, 09 Mar 2023 08:51:45 GMT

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

Re: Trelli/multiple scatter deneb

giammariam — Thu, 09 Mar 2023 09:44:08 GMT

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.

Re: Trelli/multiple scatter deneb

JimmyCruyp — Thu, 09 Mar 2023 09:56:00 GMT

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

Re: Trelli/multiple scatter deneb

giammariam — Thu, 09 Mar 2023 22:03:09 GMT

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

Re: Trelli/multiple scatter deneb

JimmyCruyp — Fri, 10 Mar 2023 08:28:20 GMT

Thanks a lot,

I sent you a lynkdin request...

Jim

Re: Trelli/multiple scatter deneb

JimmyCruyp — Fri, 10 Mar 2023 09:53:08 GMT

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

Re: Trelli/multiple scatter deneb

giammariam — Fri, 10 Mar 2023 10:30:57 GMT

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.

Re: Trelli/multiple scatter deneb

JimmyCruyp — Fri, 10 Mar 2023 10:32:09 GMT

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

Re: Trelli/multiple scatter deneb

giammariam — Fri, 10 Mar 2023 10:42:21 GMT

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.

Re: Trelli/multiple scatter deneb

giammariam — Sat, 11 Mar 2023 11:27:51 GMT

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:

Re: Trelli/multiple scatter deneb

JimmyCruyp — Mon, 13 Mar 2023 08:50:54 GMT

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 ....

Re: Trelli/multiple scatter deneb

giammariam — Mon, 13 Mar 2023 09:45:29 GMT

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