Find Max value using Measureand use it to define Max value of ScrollBar

Question

I am creating a PBI custom visual using Deneb Vegalite. Since I have many values on the Y axis, I created a scroll bar using Deneb Vega Lite code.

I created a measure to have dynamic index Rank.

Rank = RANKX(ALLSELECTED('DPM dump_Jan2023'),
    CALCULATE(SUM('DPM dump_Jan2023'[Index_ProductRel])),
    , ASC, Dense
    )

The problem is the static value of max of ScrollBar is set to 60. Instead of using static value, I would like to make it dynamic. It should be set to max value of Rank - 10.

I tried

"max": {"expr": "max(dataset, 'Rank')"}

but this is returning an error.

I also tried joinaggregate but that is also not working.

How do I make this max parameter dynamic based on Rank?

{
  "$schema": "https://vega.github.io/schema/vega-lite/v5.json",
  "width": 1000,
  "height": {"step": 35},
  "config": {
    "line": {"strokeWidth": 1}
  },
  "params": [
    {
      "name": "ScrollBar",
      "value": 1,
      "bind": {
        "input": "range",
        "min": 1,
        "max": 60,
        "step": 1
      }
    }
  ],
  "transform": [
    {
      "filter": "datum.Rank <= ScrollBar + 9 && datum.Rank >= ScrollBar"
    }
  ],
  "data": {"name": "dataset"},
  "encoding": {
    "x": {
      "field": "Date_Roadmap",
      "scale": {"padding": 30},
      "type": "temporal",
      "axis": {
        "title": "",
        "format": "%b-%Y",
        "domain": true,
        "ticks": true,
        "tickCount": 20,
        "minExtent": 50,
        "maxExtent": 20,
        "labelPadding": 0,
        "labelAngle": -45,
        "labelOverlap": false,
        "labelSeparation": 0,
        "labelFont": "Segoe UI",
        "labelFontSize": 12,
        "labelColor": "black"
      }
    },
    "y": {
      "field": "Parents Release_Final",
      "type": "nominal",
      "title": "Parent Release",
      "axis": {
        "offset": 5,
        "ticks": true,
        "labelFontSize": 12,
        "minExtent": 70,
        "domain": true,
        "grid": true,
        "gridDash": [4, 4],
        "gridColor": "#C0D8EE",
        "labelColor": "black"
      }
    }
  },
  "layer": [
    {
      "mark": "line",
      "encoding": {
        "detail": {
          "field": "Parents Release_Final",
          "type": "nominal"
        },
...

Dataset with Rank column

Sample dataset:
https://drive.google.com/file/d/1v5FJwOcqMeeZOaoV008omHaojdH52btp/view?usp=drive_link

Answer 1

Perform a transform on your data first, between the "config" and "params":

"config": {
    "line": {"strokeWidth": 1}
},
"insert ": "transform ... blaa blaa ... see below to (watch the close and comma)"],
"params": [

and then replace "max": 60 with "max": {"expr": "MaxRankValue"}.

"transform": [
    {
      "aggregate": [
        {
          "op": "max",
          "field": "Rank",
          "as": "MaxRank"
        }
      ]
    },
    {
      "calculate": "datum.MaxRank[0]",
      "as": "MaxRankValue"
    }
  ],

Answer 2

Here is an example using rank / dense_rank. It may be useful for you to understand the transform setup.

You could just replace 10 with a measure from Power BI.

{
  "$schema": "https://vega.github.io/schema/vega-lite/v5.json",
  "description": "A bar chart that sorts the y-values by the x-values.",
  "data": {"url": "data/population.json"},
  "transform": [
    {"filter": "datum.year == 2000"},
    {
      "joinaggregate": [
        {"op": "sum", "field": "people", "as": "totalPopulation"}
      ],
      "groupby": ["age"]
    },
    {
      "window": [{"op": "dense_rank", "as": "rank"}],
      "sort": [{"field": "totalPopulation", "order": "descending"}]
    },
    {"filter": "datum.rank <= 10"}
  ],
  "height": {"step": 17},
  "mark": "bar",
  "encoding": {
    "y": {"field": "age", "type": "ordinal"},
    "x": {"aggregate": "sum", "field": "people", "title": "population"}
  }
}